DAX- Calculate & Find

[AmitM]

Hi all,

I have 2 tables: "names" & "quantity" (which contain many columns, but I will present the relevant 2 in my opinion).

1. "<gs id="6872c7c6-f229-4561-af7c-235326cfe526" ginger_software_uiphraseguid="40c66b28-1472-4074-8dad-da41120db3c9" class="GINGER_SOFTWARE_mark">name</gs>" table consists unique ID (column1) and Name of person (column2).
2. "<gs id="a2501749-2bca-4389-91b2-e8794a600634" ginger_software_uiphraseguid="82983f59-e16a-40c1-8b42-88b68667ff72" class="GINGER_SOFTWARE_mark">quantity</gs>" table consists description (column1) and Qty (column2).

"description" column contains the name of the person inside a sentence- there is no predefined way where it will be located + there is no other way to make the connection between the tables.

<gs id="c3421053-fa5f-4cb6-aa1b-45fe59331e9f" ginger_software_uiphraseguid="ee9fd103-f780-49a4-82e5-446c5bd486e1" class="GINGER_SOFTWARE_mark">for</gs> Ex: description can contain the following:
1.80cm, endurance, John, AZ
OR
<gs id="190bdbc4-9442-4c1b-9bda-7c4dd5d153d1" ginger_software_uiphraseguid="a15e822a-c260-4f85-96db-6fbb3a0188fc" class="GINGER_SOFTWARE_mark">lovable</gs>, self-retained, domestic, John (different changing locations within the sentence).

I thought of using the next measure:

John<gs id="cbd1f3ef-527f-465f-9775-e91a3c80da02" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">:</gs>=CALCULATE<gs id="21f007d5-0887-499a-b962-b250a1e4ca92" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">(</gs>SUM<gs id="a8eb291d-02bb-4752-a857-bb52677051dc" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">(</gs>quantity<gs id="4528b91a-2993-428c-b38f-86bd044000b9" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">[</gs>Qty])<gs id="65f8dd90-3a63-44a4-b5ed-3cf42228b11f" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">,</gs>FIND<gs id="839be71e-a51a-438c-8a69-c0e833058d9f" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">(</gs>"John"<gs id="a2f56d15-67c8-46d8-90d1-5c0338f1ed23" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">,</gs>quantity<gs id="e67d306c-bb8a-4959-8779-35a5b97ea756" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">[</gs>Description]<gs id="5a8ac316-50fd-4195-b5aa-9962ccc9bbd9" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">,</gs>1<gs id="308bbdf4-3920-4586-aa47-8e082c31ee9c" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">,</gs>blank<gs id="da048670-db93-4c2f-bfeb-aa2be999a8ac" ginger_software_uiphraseguid="358fbf26-40eb-42aa-8a55-f96d14ac61c9" class="GINGER_SOFTWARE_mark">(</gs>))>0)

--> <gs id="48fc556a-6cb1-4332-9a4f-50bf3d12a80b" ginger_software_uiphraseguid="84657048-49bc-43e9-8a5c-929554a87105" class="GINGER_SOFTWARE_mark">will</gs> sum the Qty column in "quantity" table where "John" exists in the description.

That works, HOWEVER, I have a list of hundred names and I thought there has to be a better way not to manually create a measure for each name.

Can that be done??

Can I somehow ask <gs id="aa5a6ef5-1481-439c-b400-9a273f305e84" ginger_software_uiphraseguid="71d3f28a-ec05-4e8a-b8ac-0b8bf1e85d9a" class="GINGER_SOFTWARE_mark">powerpivot</gs> to search each row in "name" table and replace the measure accordingly to provide me the quantity purchased by each name in a pivot table?

THANKS!

<gs id="6d23788a-7202-49df-ad8d-ff0c7d0eb783" ginger_software_uiphraseguid="19caf772-2bf2-419a-b564-5eb79c574a1a" class="GINGER_SOFTWARE_mark">name</gs> table:

ID	Name
1	John
2	Amit
3	David
4	Max

<tbody>
</tbody>

<gs id="2231991d-a187-456b-817b-df616e081601" ginger_software_uiphraseguid="08960e7e-f94d-433a-8356-91a499b92ea4" class="GINGER_SOFTWARE_mark">quantity</gs> table:

<gs id="4d46c3c1-7a74-4698-9dac-93fb8f29b578" ginger_software_uiphraseguid="ed157b80-1b08-411a-b503-4828fdb7fded" class="GINGER_SOFTWARE_mark">description</gs>	Qty
<gs id="0ed5bb76-0115-445d-9662-12ec9220d143" ginger_software_uiphraseguid="b75a00e3-58c7-4c1d-b4d8-d6b5355c5c61" class="GINGER_SOFTWARE_mark">hi how</gs> are you? I<gs id="61e1e453-8b99-4e62-b209-705e3a8472c1" ginger_software_uiphraseguid="6f56d409-e19c-4abd-9f93-07d9e096c7ff" class="GINGER_SOFTWARE_mark"> ;</gs>am fine, Max, hello	2
John, 1.80, 2.20, MM	5
<gs id="112f2d05-ad5f-4860-a76d-48696b4568a9" ginger_software_uiphraseguid="4f061df0-3eca-456d-b2e0-83a1456f8d47" class="GINGER_SOFTWARE_mark">track</gs>, trace, John, add	3
<gs id="b05a2df0-b8a5-49eb-bf12-ae1cf3ddb248" ginger_software_uiphraseguid="384e0cb0-f164-421e-b8fc-cd7edb6f1ed4" class="GINGER_SOFTWARE_mark">flames</gs>, 22, 32mm, David	1

<tbody>
</tbody>

 

[AmitM]

I found a problem.

<gs id="f24084a7-4d6a-4826-96a3-d77dd501bd2f" ginger_software_uiphraseguid="d8de67f3-6d39-4c6e-bcaf-af2c1e1786b0" class="GINGER_SOFTWARE_mark">when</gs> using measures I can't add them also to the rows/columns in the pivot table, hence I can't connect between "John" in the quantity table and John in another different table, where there is no need to use the "search" function.

Example-

2nd table:

<gs id="4d46c3c1-7a74-4698-9dac-93fb8f29b578" ginger_software_uiphraseguid="ed157b80-1b08-411a-b503-4828fdb7fded" class="GINGER_SOFTWARE_mark"><gs id="95b350b3-ac53-4e6f-a5d9-1f14e92071e8" ginger_software_uiphraseguid="f0afaa17-11d9-43d5-9c2e-6f674597b48f" class="GINGER_SOFTWARE_mark">description</gs></gs>	Qty
Max	3
John	2
John	2
David	1

<tbody>
</tbody>

Instead of getting the desired output:

<gs id="58946c5a-0412-4cc7-a66c-05641878827b" ginger_software_uiphraseguid="1298da36-2acb-4f46-bce6-4270f13e9c8a" class="GINGER_SOFTWARE_mark">name</gs>	<gs id="e46c2299-4602-4dab-9126-3e413495f1bd" ginger_software_uiphraseguid="f82dc634-fec2-4bc6-810c-59872b543a95" class="GINGER_SOFTWARE_mark">qty</gs>
John	8+2+2=12
Max	3+2=5
David	1+1=2
Amit	0+0=0

<tbody>
</tbody>

I get the following:

<gs id="4ec65a61-f61c-4883-a3ba-7c33d0e5d8c7" ginger_software_uiphraseguid="68297e0f-fa38-4ec6-bc06-d3b0e20df75c" class="GINGER_SOFTWARE_mark">name</gs>	<gs id="bd2ff5c0-7e57-43b4-92ec-4a0d50b2f2ab" ginger_software_uiphraseguid="18a0af2e-e41c-4ac9-9acd-a36caf904cb6" class="GINGER_SOFTWARE_mark">qty</gs>	John	Amit	David	Max
John	4	8
Amit	0
Max	3				2
David	1			1

<tbody>
</tbody>

 

[scottsen]

So... I think what you need here is some Power Query to clean up your data, before it gets to Power Pivot. I mean, there are other ways we can force things to work out (I would add a calc column on the quanity table that has just the name of the person, making your problem easier)... but if you can do that in PQ, you will experience greater joy in life

 

[AmitM]

THX!

I wasn't aware this option is free to download. I am trying to use it but have 0 experience.
Can you guide me what should I do or if you can refer me to a good data source?

Appreciate your help!

 

[AmitM]

The thing that I am not sure about is that I will probably need to use "Add Column" and then "Add Custom Column" and then build a formula.

Will the formula be a nested IF(find("John",description),"John",if(find("Amit",...)))?

that will be a hugh un-maintained table...

I believe that in this part you had a better suggestion

thx, @scottsen

 

[scottsen]

I'm not good enough with Power Query to property advise. If you want, i can try and get Chris Webb or Ken Puls in here to help

But if you want to stick w/ Power Pivot, I added a calc column to the description table that did what you want. I think

=CALCULATE(VALUES(Person[Name]), FILTER(Person, SEARCH(Person[Name], Quantity[description], 1, 0)))

 

[AmitM]

</gs>I'm not good enough with Power Query to property <gs id="8f231cb3-7200-41ff-b628-2eb12c351c2c" ginger_software_uiphraseguid="bdc7de83-ca46-43f3-9f91-bb719ddd809e" class="GINGER_SOFTWARE_mark">advise</gs>. If you want, <gs id="e9ad0e06-367c-4909-b681-a03f4cfb8158" ginger_software_uiphraseguid="e45acc72-f3bb-41ec-a349-9a62a17c68a9" class="GINGER_SOFTWARE_mark">i</gs> can try and get Chris Webb or Ken Puls in here to help

But if you want to stick w/ Power Pivot, I added a <gs id="08d6eaea-505a-4340-9207-584fa1693681" ginger_software_uiphraseguid="1f7c60ae-38f4-43de-affb-312738eed966" class="GINGER_SOFTWARE_mark">calc</gs> column to the description table that did what you want. I think
[<gs id="ec2dafad-ed2f-4fbe-aa20-f3fa8e4a443c" ginger_software_uiphraseguid="0aec1d23-fde9-4dc1-b214-75600bbb9f11" class="GINGER_SOFTWARE_mark">code</gs>]
=CALCULATE(VALUES(Person[Name]), FILTER(Person, SEARCH(Person[Name], Quantity[description], 1, 0)))
[/<gs id="50496bef-4412-445e-963e-8960aaa97164" ginger_software_uiphraseguid="e045d739-7ced-4c19-ab8f-ebe801f9149c" class="GINGER_SOFTWARE_mark">code</gs><gs id="9d8ca48c-4306-49b4-a546-dce232c26a97" ginger_software_uiphraseguid="e045d739-7ced-4c19-ab8f-ebe801f9149c" class="GINGER_SOFTWARE_mark">]</gs>

I ran it now in the <gs id="82f8399a-365c-4ab6-bc8d-a4589e5f681c" ginger_software_uiphraseguid="4a792e1a-8c1a-4710-b390-6d6d447858df" class="GINGER_SOFTWARE_mark">model but</gs> got an ERROR signal.

<gs id="bb3de69e-d805-45a3-b0a8-c662819b64cd" ginger_software_uiphraseguid="2d3c8a26-293a-4a04-a691-c89d38269e1f" class="GINGER_SOFTWARE_mark">any</gs> idea?

#Error- The value for column 'description' in table '<gs id="b3cf085d-da27-4930-accb-d6d3cb5732dc" ginger_software_uiphraseguid="3c79a710-75ad-4e5b-849c-f8dd0470edbb" class="GINGER_SOFTWARE_mark">Quantity</gs>' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

 

[scottsen]

As a calc column on the quanity table?

 

[AmitM]

yes

 

[scottsen]

You want to share your workbook (email it to me, or use dropbox/onedrive/googledrive to share a link, or whatever) ?