topic Re: DAX self join query to get distinct count based on filter criteria in DAX Commands and Tips

DAX self join query to get distinct count based on filter criteria

Msri — Wed, 13 Nov 2024 18:17:03 GMT

Hi All,

I am getting stuck in converting a SQL query to DAX.

I want distinct count of ColumnA when all IDs in columnB has value = 'NK'(ColumnC). This needs to be checked for every Date(grouped by date). So from the below example, for 11/8/2024, the DAX needs to return the total of 1(Because only 145 has all column B values with NK and the others have a mix of K and NK). For 11/1/2024, the total will be 0. Basically counting Column A when all the grouped values in Column B have NK

This DAX is used in a matrix which splits these counts based on aging bucket and area.

Greatly appreciate the help.

The SQL that wrote is as below (wrote based on checking the negative condition)

select distinct ColumnA from TableA

(

(select count(1) from TableA TA1 where TA.ColumnA = TA1.ColumnA and TA.Date=TA1.Date and (TA1.ColumnB='K' or TA1.columnA is null)

)=0

)

Table A

Date	ColumnA	ColumnB	ColumnC
11/8/2024	123	345	K
11/8/2024	123	333
11/8/2024	145	678	NK
11/8/2024	145	666	NK
11/8/2024	145	786	NK
11/8/2024	157	567	NK
11/8/2024	157	555	K
11/1/2024	145	666	K
11/1/2024	145	786	NK

Re: DAX self join query to get distinct count based on filter criteria

AntrikshSharma — Wed, 13 Nov 2024 19:12:01 GMT

Msri This works?

Measure 2 = INT ( COUNTROWS ( Msri ) = CALCULATE ( COUNTROWS ( VALUES ( Msri[ColumnB] ) ), Msri[ColumnC] = "NK" ) )

Re: DAX self join query to get distinct count based on filter criteria

Msri — Wed, 13 Nov 2024 20:07:57 GMT

Thanks for the reply. I added the above measure, but I am getting 0 as total. From SQL I got 9 rows

Re: DAX self join query to get distinct count based on filter criteria

FreemanZ — Wed, 13 Nov 2024 20:37:38 GMT

hi Msri ,

try like:

measure =

VAR _table =

ADDCOLUMNS(

SUMMARIZE(

data,

data[date],

data[ColumnA]

"ColC",

CALCULATE(

CONCATENATEX(

VALUES(data[ColumnC]),

data[ColumnC], ", "

)

VAR _result =

COUNTROWS(

FILTER(

_table,

[ColC]="NK"

)

RETURN _result

Re: DAX self join query to get distinct count based on filter criteria

Msri — Thu, 14 Nov 2024 01:03:33 GMT

FreemanZ : Thanks a lot, the solution works perectly