DISTINCT COUNT with condition on measure

[Rinn]

Hi,

I have an issue with my DAX calculation. 

My visual input table is like that : 

Year	ID	[calculated %] DAX measure
2020	a	41%
2020	b	35%
2021	a	42%
2021	b	43%
2022	b	12%
2022	c	34%

I am trying to distinct count the number of IDs that have a calculated % higher than a parameter [user %] (slicer that users can choose themself).

So my visual output table would be : (if parameter [user %] = 40%)

Year	Distinct count IDs
2020	1
2021	2
2022	0

What I tried to do is :

Distinct count IDs = 

CALCULATE(    DISTINCTCOUNT(table[Id]), FILTER(table, [calculated %] > parameter[user %])    )

but my measure is blank when I try to put it in a visual table...

Can you help me pls ?

Best Regards.

[Anonymous · Accepted solution]

Hi, @Rinn 

You can try the following methods.

Measure = CALCULATE(DISTINCTCOUNT('Table'[ID]), FILTER(ALLEXCEPT('Table','Table'[Year]), [calculated %] > SELECTEDVALUE(Parameter[Parameter])))+0

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rinn]

Hi @Anonymous , thanks for your reply.

Actually, for your example, I tried with my real DAX measure [calculated %] (sorry for not posting it earlier) which is :

calculated % =

var _prev= CALCULATE(SUM('Table'[Value]), PREVIOUSYEAR('Table'[Year]))

RETURN

DIVIDE(SUM('Table'[Value]), _prev)-1

And I did not work unfortunately.

Do you know why ?

Edit : the variable _prev seems to be the reason of the disfunctioning of the calculation

Regards.

[Rinn]

The response of @Anonymous is a good solution.

For my particular problem, I separated calculated % into 3 measures :

1. CALCULATE(SUM('Table'[Value]), PREVIOUSYEAR('Table'[Year]))

2. SUM('Table'[Value]

3. DIVIDE(1., 2.)-1

And it works well.

Thank you !

Regards

[AmiraBedh]

Try to create a query new blank query and type = MYPARAMETER, 

Than you can reference in columns/dax measures

https://community.fabric.microsoft.com/t5/Desktop/How-to-reference-a-parameter-to-a-DAX-function/m-p...