Distinct count of values in column if multiple strings exist in other columns

[Chris2016]

Hello,

I have a scenario that is similar to the example below. I need help to count the distinct number of Stores that have the type "Local Fruit" and the subtype "Apples" and "Pears". In the below example, the distinct count is 2: "Supermarket" and "Local store", as they are the only ones who have the "Local Fruit" as both "Apples" and "Pears". Any idea how to translate this into a DAX measure? -- Thanks so much!

Stores	Type	Subtype
Supermarket	Local Fruit	Apples
Supermarket	Local Fruit	Pears
Supermarket	Local Fruit	Grapes
Supermarket	Exotic Fruit	Bananas
Supermarket	Exotic Fruit	Pineapple
Supermarket	Exotic Fruit	Coconuts
Farmers Market	Local Fruit	Apples
Farmers Market	Local Fruit	Grapes
Farmers Market	Exotic Fruit	Bananas
Farmers Market	Exotic Fruit	Pineapple
Local store	Local Fruit	Apples
Local store	Local Fruit	Pears
Local store	Local Fruit	Grapes
Local store	Exotic Fruit	Bananas
Local store	Exotic Fruit	Pineapple
Shopping Center	Local Fruit	Pears
Shopping Center	Local Fruit	Grapes
Shopping Center	Exotic Fruit	Bananas
Shopping Center	Exotic Fruit	Pineapple
Shopping Center	Exotic Fruit	Coconuts

[johnt75 · Accepted solution]

You could create a measure like

Num matches =
VAR StoresWithCount =
    ADDCOLUMNS (
        VALUES ( 'Table'[Stores] ),
        "@count",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Type] = "Local fruit",
                TREATAS ( { "Apples", "Pears" }, 'Table'[Subtype] )
            )
    )
VAR Result =
    COUNTROWS ( FILTER ( StoresWithCount, [@count] = 2 ) )
RETURN
    Result

[FreemanZ · Accepted solution]

hi @Chris2016 ,

there was some typo in my original post, the following measures shall also work:

measure2 = 
VAR _table1 =
ADDCOLUMNS(
    SUMMARIZE(
        data, 
        data[Stores],
        data[Type]
    ),
   "Subtypes",
	CALCULATE(
       CONCATENATEX(
        VALUES(data[Subtype]), data[Subtype], ", "
       )
	)
)
VAR _table2=
FILTER( 
    _table1,
    data[Type]="Local Fruit"
       &&CONTAINSSTRING([Subtypes], "Apples")
       &&CONTAINSSTRING([Subtypes], "Pears")
)
RETURN COUNTROWS(_table2)

or 

measure = 
SUMX(
    VALUES(data[stores]),
    VAR _typelist1 = CALCULATETABLE(VALUES(data[type]))
    VAR _typelist2 = CALCULATETABLE(VALUES(data[Subtype]))
    RETURN
        IF(
            "Local Fruit" IN _typelist1
                &&"Apples" IN _typelist2
                &&"Pears" IN _typelist2,
            1
        )
)

 they worked like:

[v-kongfanf-msft]

Hi @Chris2016 ,

Did @sanalytics  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[FreemanZ]

hi @Chris2016 ,

try also like:measure =

VAR _table1 =

ADDCOLUMNS(

    SUMMARIZE(

        data, 

        data[Stores],

        data[Type]

    ),

   "Subtypes",

    CALCULATE(

       CONCATENATEX(

         VALUES(data[Subtype]), ", "

       )

    )

)

VAR _table2=

FILTER( 

    _table1,

    data[Stores]="Local Fruit"

       &&CONTAINSSTRING([Subtypes], "Apples"

      &&CONTAINSSTRING([Subtypes], "Pears"

)

VAR _result = COUNTROWS(_table2)

RETURN _result

[FreemanZ · Accepted solution]

hi @Chris2016 ,

there was some typo in my original post, the following measures shall also work:

measure2 = 
VAR _table1 =
ADDCOLUMNS(
    SUMMARIZE(
        data, 
        data[Stores],
        data[Type]
    ),
   "Subtypes",
	CALCULATE(
       CONCATENATEX(
        VALUES(data[Subtype]), data[Subtype], ", "
       )
	)
)
VAR _table2=
FILTER( 
    _table1,
    data[Type]="Local Fruit"
       &&CONTAINSSTRING([Subtypes], "Apples")
       &&CONTAINSSTRING([Subtypes], "Pears")
)
RETURN COUNTROWS(_table2)

or 

measure = 
SUMX(
    VALUES(data[stores]),
    VAR _typelist1 = CALCULATETABLE(VALUES(data[type]))
    VAR _typelist2 = CALCULATETABLE(VALUES(data[Subtype]))
    RETURN
        IF(
            "Local Fruit" IN _typelist1
                &&"Apples" IN _typelist2
                &&"Pears" IN _typelist2,
            1
        )
)

 they worked like:

[Chris2016 · Accepted solution]

Hello! Thanks a lot for your ideas, the second one did not work in my dataset, but I tweeked the first one to give me the results I need (i will use this one, as I realised the numbers were wrong in the first solution I accepted.)

Measure 3 =
VAR _table1 =
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
        'Table',
        'Table'[Stores],
        'Table'[Type]
    ),
   "SubTypes",
    CALCULATE(
       CONCATENATEX(
        VALUES('Table'[Subtype]), 'Table'[Subtype], ", "
         )
        )
    ),  FILTER('Table', [Type] = "Local Fruit" && ([Subtype]= "Apples" || [Subtype]= "Pears") )
)
var result = COUNTROWS (
   DISTINCT (
       SELECTCOLUMNS (
       FILTER ( _table1,   [SubTypes] = "Apples, Pears" || [SubTypes] = "Pears, Apples"),
   "@result", [Stores])
    )
   )
return result

Thanks so much for everyone's help!
Best regards!

[FreemanZ]

hi @Chris2016 ,

i thought the 2nd one might be more preferable. could you tell more about how it does not work?

[Chris2016]

Hi, thanks for following up. The measure works just fine in the sample dataset, but in my dataset it is counting way more "stores" than necessary.
I am now trying to do a calculated column to flag the "Types" as per the same scenario inside my table (to use it as a slicer), but it's not working, any ideas on that?
Thanks a lot!

[sanalytics]

@Chris2016 

You can use below  measure it order to show distinct count 

Count = 
VAR _Table = 
FILTER(
	'Table',
	'Table'[Type] = "Local Fruit" &&
	'Table'[Subtype] = "Apples" ||
	'Table'[Subtype] = "Pears"
	
	)
VAR _Result = 
COUNTROWS(
    _Table
)
RETURN
_Result

Below screenshot

pbix file is attached below

https://we.tl/t-6798YDeEe4

Regards

sanalytics

[Chris2016]

Thanks a lot for your reply, the solution does not work for me, as I was looking for a way of distinct-counting the stores, and only two have the criteria specified in the sample dataset, but the measure is counting 6:

I appreciate the effort, I will use John's solution above.

Best regards!

[johnt75 · Accepted solution]

You could create a measure like

Num matches =
VAR StoresWithCount =
    ADDCOLUMNS (
        VALUES ( 'Table'[Stores] ),
        "@count",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                'Table'[Type] = "Local fruit",
                TREATAS ( { "Apples", "Pears" }, 'Table'[Subtype] )
            )
    )
VAR Result =
    COUNTROWS ( FILTER ( StoresWithCount, [@count] = 2 ) )
RETURN
    Result

[Chris2016]

Thanks so much, John, this worked in my dataset! 
Best regards!