Forum Discussion

ravdha85's avatar
ravdha85
Frequent Visitor
3 years ago
Solved

Compare column values and output the result using DAX

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
    ravdha85
    3 years ago

    Thanks Anonymous  that worked🙂

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
3 years ago

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")