self join
1 Topic
DAX self join query to get distinct count based on filter criteria
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 NKSolved925Views0likes4Comments