How to Count Unique Customers Based on Only Having Specific Values

[Indo]

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
Bill	Fruit
Chris	Meat
Chris	Vegetable
Derek	Vegetable
Derek	Fruit
Eric	Vegetable
Francine	Meat
Francine	Fruit
Francine	Vegetable

How would I count the number of unique customers that only have a food group that is either fruit or vegetable, or both. For example, Aaron, Bill, Derek, Eric would be considered, so 4 total.

Also, how would I count the number of unique customers that have a food group that is meat and either fruit or vegetable or both. In this case, only Chris and Francine is considered, in total of 2 customers.

Thank you!

[Indo]

Hello all,

I tried all of your solutions to my real dataset but the second measure for counting a food group that is meat and either fruit or vegetable or both doesn't account for a customer that has 2 or more of each. For example,

Table1

Customer Name	Food Group
Aaron	Fruit
Bill	Fruit
Chris	Meat
Chris	Vegetable
Derek	Vegetable
Derek	Fruit
Eric	Vegetable
Francine	Meat
Francine	Fruit
Francine	Vegetable
George	Meat
George	Meat
George	Vegetable
George	Fruit
Heather	Meat
Heather	Meat
Isabel	Fruit
Isabel	Meat
Isabel	Fruit
Isabel	Fruit
Joe	Meat
Joe	Meat
Joe	Vegetable
Kevin	Meat

In this updated table, George, Isabel and Joe should be considered in the second measure.

Also, since there is a total of 11 unique customers and Heather and Kevin doesn't fit in either measures, (1st measure has Aaron, Bill, Derek, Eric {4 total}), (2nd measure has Chris, Francine, George, Isabel, Joe {5 total}, is it correct to assume that the difference between the total unique customers and sum of both the measures is the total number of customers that only have a food group of meat, which is 2 in this case (Heather, Kevin)?

In my real dataset, it is substantially bigger, so the difference isn't clearer to me if I could make that assumption.

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

Dowload my PBI file from here.

Hope this helps.

[Indo]

Thank you. This is what I was looking for!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Jihwan_Kim]

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 )

[ryan_mayu]

@Indo 

you can try this

Measure = DISTINCTCOUNT('Table'[Customer Name])- CALCULATE(DISTINCTCOUNT('Table'[Customer Name]),FILTER('Table','Table'[Food Group]="Meat"))

Measure 2 =

VAR TBL=DISTINCT(FILTER('Table','Table'[Food Group]="Meat"))

VAR tbl2=ADDCOLUMNS(TBL,"CHECK",CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Customer Name]=[Customer Name])))

RETURN COUNTROWS(FILTER(tbl2,[CHECK]<>1))