Forum Discussion

poweruser55's avatar
poweruser55
Helper IV
4 years ago

Distinct count two measures

How can I count distinct numbers within measures or variables. There are two ways I can do this combine at the measures into another measure or make one measure and create variables. I have 9 different measures right now with this. 

Measure 1  = CALCULATE(
DISTINCTCOUNT( '999_Funnel'[OLI Number] ),
'999_Funnel'[FC (Flag)] = "Y",
'999_Funnel'[OLI.Status] = "Active")
Measure 2 = CALCULATE(
DISTINCTCOUNT( '999_Funnel'[OLI Number] ),
'999_Funnel'[Another (Flag)] = "Y",
'999_Funnel'[OLI.Status] = "Active")
How would I add the count of the oli numbers together so that there is no overlap between the 9 measures, here are two as an example.

I need to add all of the measure together but I do not want the OLI number to be double counted. How can I add all of these measure together so that each distinct oli number is only counted once even if it occurs in other measures.

Need Help

1 Reply

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    poweruser55 

    You can write a totaling measure that takes into account all the flags.  || = OR in DAX so something like this.

    Total Count =
CALCULATE (
    DISTINCTCOUNT ( '999_Funnel'[OLI Number] ),
    '999_Funnel'[OLI.Status] = "Active" && 
	( '999_Funnel'[FC (Flag)] = "Y" || '999_Funnel'[Another (Flag)] = "Y" )
)