Measure to remove (empty) from Distinct count

[Bokazoit]

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 💟

[v-jingzhang]

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

[Greg_Deckler]

@Bokazoit Maybe:

Unique respondents = COUNTROWS(DISTINCT(FILTER(FactHjertelinjen,[HjertelinjenKey]<>BLANK())))

[Bokazoit]

Thank You but that did not change anything