Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to Count Unique Customers Based on Only Having Specific Values

Hello,   I'm new to Power BI and can't wrap my head around this. I attached a sample table closely related to what I'm trying to figure out.   Table1 Customer Name Food Group Aaron Fruit...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Dowload my PBI file from here.

    Hope this helps.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

please check the below picture and the attached pbix file.

 

 

Question one measure: = 
VAR newtable =
    SUMMARIZE (
        FILTER ( Data, Data[Food Group] IN { "Fruit", "Vegetable" } ),
        Data[Customer Name]
    )
VAR othertable =
    SUMMARIZE (
        FILTER ( Data, Data[Food Group] IN { "Meat" } ),
        Data[Customer Name]
    )
VAR resulttable =
    EXCEPT ( newtable, othertable )
RETURN
    CONCATENATEX ( resulttable, Data[Customer Name], ", " ) & " / "
        & COUNTROWS ( resulttable )

 

Question two measure: = 
VAR newtable =
    SUMMARIZE (
        FILTER ( Data, Data[Food Group] IN { "Fruit", "Vegetable" } ),
        Data[Customer Name]
    )
VAR othertable =
    SUMMARIZE (
        FILTER ( Data, Data[Food Group] IN { "Meat" } ),
        Data[Customer Name]
    )
VAR resulttable =
    INTERSECT( newtable, othertable )
RETURN
    CONCATENATEX ( resulttable, Data[Customer Name], ", " ) & " / "
        & COUNTROWS ( resulttable )