topic Re: Distinct count of values in column if multiple strings exist in other columns in DAX Commands and Tips

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

Chris2016 — Wed, 04 Dec 2024 13:21:33 GMT

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

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

johnt75 — Wed, 04 Dec 2024 13:42:37 GMT

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

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

sanalytics — Wed, 04 Dec 2024 14:59:24 GMT

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

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

FreemanZ — Wed, 04 Dec 2024 15:10:14 GMT

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

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

Anonymous — Thu, 05 Dec 2024 09:36:00 GMT

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

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

Chris2016 — Thu, 05 Dec 2024 15:00:50 GMT

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

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

Chris2016 — Fri, 06 Dec 2024 08:47:16 GMT

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!

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

FreemanZ — Fri, 06 Dec 2024 07:00:16 GMT

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)

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:

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

Chris2016 — Fri, 06 Dec 2024 16:35:38 GMT

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!

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

FreemanZ — Fri, 06 Dec 2024 14:44:57 GMT

hi Chris2016 ,

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

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

Chris2016 — Fri, 06 Dec 2024 16:38:31 GMT

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!