Distinct count of items in a column except the one selected from slicer DAX

Question

I am trying to write a dax for the following query:

select EncounterID
,EncounterDateKey 
,DxCode
,DxID  
into #PatEnc
from table1
where EncounterDateKey = 20180601
  and DxCode = 'Z00.00'

select count(distinct(dbf.DiagnosisCode))
from #PatEnc    
 left outer join tabel1 dbf on dbf.EncounterID = 
  #PatEnc.EncounterID
 where dbf.DxCode != 'Z00.00'

My fact table has following structure

EncounterID      EncounterDateKey     DxCode     DxID
1                20180601             Z00.00      1
1                20180601             Z00.01      2
1                20180601             D00.00      3
2                20180601             A00.0       4
2                20180601             Z00.00      1
3                20180601             B00.00      5
3                20180601             F0.0        6

so the Dax code should return the count as 3 as there are 3 different Dx Code for the Encounters that were selected when Z00.00 was selected from the slicer. I tried filter and except function for the DAX code but its not brigning the accurate results.

I get the value selected in the slicer but couldn't get the count of remaining values. Any help is appreciated.

Olly Olly · Accepted Answer · 2018-12-04T14:58:40

Answer edited, after question updated:

This query returns a distinct count of non-selected DxCode(s) for Encounter ID(s) associated with the selected DxCode(s):

Count Unselected = 
VAR SelectedDxCodes = VALUES ( Table1[DxCode] )
VAR SelectedEncounterIDs = VALUES ( Table1[EncounterID] )
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( Table1[DxCode] ),
        ALL ( Table1[DxCode] ),
        ALL ( Table1[EncounterID] ),
        NOT ( Table1[DxCode] IN SelectedDxCodes ),
        Table1[EncounterID] IN SelectedEncounterIDs
    )

It currently returns BLANK if no DxCode slicer selection is made, as this case isn't explained in your question.

Distinct count of items in a column except the one selected from slicer DAX

1 Answers