Tabke with multiple conditions - does value contain

[Anonymous]

Hi all,

I have a request that is somewhat hard to explain in a topic, but I will try.

We have a "Supplier" table containing "Company", "Supplier Group", "Supplier Name", "Language" and "Job Type":

Company	Supplier Group	Supplier Name	Language	Job Type
Best Buy	Primary	John	English	Normal
Best Buy	Primary	John	English	Advanced
Best Buy	Primary	John	Dutch	Normal
Best Buy	Primary	John	Dutch	Advanced
Best Buy	Primary	John	German	Normal
Best Buy	Primary	John	Finnish	Normal
Best Buy	Primary	Michelle	English	Normal
Best Buy	Primary	Michelle	English	Advanced

We are interested in finding out:

What Company/Supplier Group/Language combinations include "Advanced" Job Type.

Output could be like:

Company	Supplier Group	Supplier Name	Language	Has Advanced?
Best Buy	Primary	John	English	Yes
Best Buy	Primary	John	Dutch	Yes
Best Buy	Primary	John	German	No
Best Buy	Primary	John	Finnish	No
Best Buy	Primary	John	Danish	No
Best Buy	Primary	Michele	English	Yes

So in the above table we can see that for Company: "Best Buy", Supplier Group: "Primary" and Language: "English", we have 2/2 suppliers with "Advanced". So we would like to show that.

So basically I would like - in a matrix - to show ALL suppliers per Company/Supplier Group/Language combination and then a measure(or something) saying "Has Advances" Yes/No. So we can see for each Company/Supplier Group/Language all the suppliers, and whether they have it or not.

Thanks!

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

You can add a new column:

Advanced = 
VAR _Adv = CALCULATE(
    COUNTROWS('Table'),
    'Table'[Job Type]="Advanced")
RETURN

IF(_Adv>0,"YES","NO")

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[amitchandak]

@Anonymous  not able to match logic, like there are additional languages for John, also there only one advance for English . So not very clear.

You can new columns like this

countx(filter(Table,[Supplier Name] =earlier([Supplier Name]) && [Language] =earlier([Language])),[Supplier Name] )

countx(filter(Table,[Supplier Name] =earlier([Supplier Name]) && [Language] =earlier([Language]) && [Job Type] = "Advanced"),[Supplier Name] )

or var in column calculation and take decision