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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Amit[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Max[/TD]
[/TR]
</tbody>[/TABLE]

<gs id="2231991d-a187-456b-817b-df616e081601" ginger_software_uiphraseguid="08960e7e-f94d-433a-8356-91a499b92ea4" class="GINGER_SOFTWARE_mark">quantity</gs> table:
[TABLE="width: 500"]
<tbody>[TR]
[TD]<gs id="4d46c3c1-7a74-4698-9dac-93fb8f29b578" ginger_software_uiphraseguid="ed157b80-1b08-411a-b503-4828fdb7fded" class="GINGER_SOFTWARE_mark">description</gs>[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]<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[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]John, 1.80, 2.20, MM[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]<gs id="112f2d05-ad5f-4860-a76d-48696b4568a9" ginger_software_uiphraseguid="4f061df0-3eca-456d-b2e0-83a1456f8d47" class="GINGER_SOFTWARE_mark">track</gs>, trace, John, add[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]<gs id="b05a2df0-b8a5-49eb-bf12-ae1cf3ddb248" ginger_software_uiphraseguid="384e0cb0-f164-421e-b8fc-cd7edb6f1ed4" class="GINGER_SOFTWARE_mark">flames</gs>, 22, 32mm, David[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[AmitM]

Hi Scottsen,

I sent you a link in a private msg.


Can attach it also here if there is anyone who can help.

<gs id="cd1e8dfe-0a0f-4b0d-9bee-da5503a53449" ginger_software_uiphraseguid="6276c46c-f2c9-462c-8793-3b204b90e271" class="GINGER_SOFTWARE_mark">thx</gs>

 

[scottsen]

I changed the VALUES() call to FIRSTNONBLANK to deal with situations where the search finds mulitple matches.

=CALCULATE(FIRSTNONBLANK(IndexTbl[Scope], 1), FILTER(IndexTbl, SEARCH(IndexTbl[Scope], DataTbl_2[Description], 1, 0)))

 

[AmitM]

WOW!!!!!


U R THE BEST!!!!

THANK U SO MUCH!

Honestly, I really appreciate your help.

Best regards,
Amit