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

[Msri]

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

[FreemanZ · Accepted solution]

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

[Msri]

@FreemanZ : Thanks a lot, the solution works perectly

[Msri]

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

[AntrikshSharma]

@Msri This works?

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