Forum Discussion

Bokazoit's avatar
Bokazoit
Continued Contributor
4 years ago

Measure to remove (empty) from Distinct count

I have this datamodel with cross filter set to Both way joins, since its a questionary with multiple choice answers. I find that data model as a solution in this forum:

 

When I need to show the amount unique respondents to a question for a periode as shown in the figure below:

I can use the filter 'Is not Blank' for the answer to the question, and I get the correct number. But is there filter in the measure I can use instead?

My measure is:

 

Unique respondents = DISTINCTCOUNT(FactHjertelinjen[HjertelinjenKey])
 
And as can be seen from the datamodel I have 6 questions with cross filter set to both way. The issue is that when somebody use 'Analyze in Excel' the measure shows a higher number and therefor the user has to add the question dimension to the filter and choose filter, then 'Does not contain' (empty)
 
I can understand why it works that way, but can't figure out the filter. I tried this:
 
Unique respondents =
CALCULATE(
DISTINCTCOUNT(FactHjertelinjen[HjertelinjenKey]),
FILTER(Question1,Question1[HjertelinjenKey] <> BLANK()))

But if I add the other 5 dimensions I removes a lot, so I would need something that says:
 
FILTER(DimQuestion1,DimHjertesygdom[HjertelinjenKey] <> BLANK())
OR
FILTER(DimQuestion2,DimHjertesygdom[HjertelinjenKey] <> BLANK())
OR
FILTER(DimQuestion3,DimHjertesygdom[HjertelinjenKey] <> BLANK())
OR
etc.
 
In the above Question1 is the filter 'Hvilken Hjertesygdom', meaning what heart disease
 
Any help is appreciated 💟
 
Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Bokazoit Maybe:

    Unique respondents = COUNTROWS(DISTINCT(FILTER(FactHjertelinjen,[HjertelinjenKey]<>BLANK())))
    • Bokazoit's avatar
      Bokazoit
      Continued Contributor
      4 years ago

      Thank You but that did not change anything

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Bokazoit 

     

    Maybe we can think the other way around. Calculate the dictinct count of empty ones and the distinct count of all values in advance. Then substract the former one from the latter one. 

     

    Best Regards,
    Community Support Team _ Jing