Compare column values and output the result using DAX

[ravdha85]

Hi Experts,

I came across a scenario where I need to findout the restaurants that are serving the same items , if you consider the below dataset the item Pizza is server by restaurants ABC,DEF,XYZ. Kindly let me know if you need any more inputs, Thanks in advance for your expert assists.

Restaurant	Items
ABC	Pizza
ABC	Burger
ABC	Noodles
XYZ	Pizza
XYZ	Frenchfries
DEF	Pizza
DEF	Juice
GHI	Coffe
GHI	Tea

Expected o/p

Restaurant	Items	Final Result
ABC	Pizza	ABC
ABC	Burger
ABC	Noodles
XYZ	Pizza	XYZ
XYZ	Frenchfries
DEF	Pizza	DEF
DEF	Juice
GHI	Coffe
GHI	Tea

[Anonymous · Accepted solution]

Hi @ravdha85 ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR tmp =
    SUMMARIZE ( ALL ( 'Table' ), 'Table'[Items], "Cnt", COUNTROWS ( 'Table' ) )
VAR tmp1 =
    CALCULATETABLE ( VALUES ( 'Table'[Items] ), FILTER ( tmp, [Cnt] >= 2 ) )
VAR _rst =
    SELECTEDVALUE ( 'Table'[Restaurant] )
VAR _item =
    SELECTEDVALUE ( 'Table'[Items] )
RETURN
    IF ( _item IN tmp1, _rst, BLANK () )

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grazitti_sapna]

Hi

You can create a calculated table with the help of the following formula. It will create a table where the restaurants are serving Pizza’s

Final Result = FILTER (All (Restaurant), Restaurant[Items]="Pizza")

[Anonymous · Accepted solution]

Hi @ravdha85 ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR tmp =
    SUMMARIZE ( ALL ( 'Table' ), 'Table'[Items], "Cnt", COUNTROWS ( 'Table' ) )
VAR tmp1 =
    CALCULATETABLE ( VALUES ( 'Table'[Items] ), FILTER ( tmp, [Cnt] >= 2 ) )
VAR _rst =
    SELECTEDVALUE ( 'Table'[Restaurant] )
VAR _item =
    SELECTEDVALUE ( 'Table'[Items] )
RETURN
    IF ( _item IN tmp1, _rst, BLANK () )

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ravdha85 · Accepted solution]

Thanks @Anonymous  that worked🙂