Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
After many attempts I still can't work this one out.
I have a report filter on Observations[user]. This has to apply to all pages, including the one of the measure below, to show only the performance of one selected user. This works fine so far.
Next, in order to calculate the progress of the selected user, I want to compare his performance to the world total species. This could for example be: 120 species found by user, 1000 species worldwide, so this user would be at 12% progress.
I've tried the measure below to calculate those 1000 species worldwide:
Total species worldwide =
CALCULATE(
DISTINCTCOUNT( Taxonomy[id] ) ,
REMOVEFILTERS( Observations[user] ) ,
FILTER( Taxonomy ,
Taxonomy[Rarity] <> 0
&& Taxonomy[Rarity] <> 4
&& Taxonomy[Status] = "Native"
&& Taxonomy[Type] = "S")
)
But REMOVEFILTERS doesn't work. The outcome of this measure is 120 species worldwide. The report filter on Observations[user] still applies, and filters the related Taxonomy table. Any user's progress becomes 100%.
Help please!
Solved! Go to Solution.
Thanks. Though not exactly like you intended, I removed the filter to a new calculation level. It worked.
Total species worldwide =
CALCULATE(
CALCULATE (
DISTINCTCOUNT( Taxonomy[id] ) ,
FILTER( Taxonomy ,
Taxonomy[Rarity] <> 0
&& Taxonomy[Rarity] <> 4
&& Taxonomy[Status] = "Native"
&& Taxonomy[Type] = "S")
) ,
REMOVEFILTER ( Data[user] )
)
Did you already tried using ALL on Taxonomy or Observations[user] instead of removefilter?
Yes, I tried:
...in place of removefilter.
They all have no effect. The report filter on Observartions[user] still applies on the number of total world species (showing 120 instead of 1000).
Example:
Total species worldwide =
CALCULATE(
DISTINCTCOUNT( Taxonomy[id] ) ,
ALL( Observations ) , ALL( Taxonomy ) , ALL ( ...every other table in the report... ),
FILTER( Taxonomy ,
Taxonomy[Rarity] <> 0
&& Taxonomy[Rarity] <> 4
&& Taxonomy[Status] = "Native"
&& Taxonomy[Type] = "S")
)
I don't know then, sounds strange, looks like some other filters are applied. Could you load the pbix?
Why also do you use filter inside calculate? try to remove it
Thanks. Though not exactly like you intended, I removed the filter to a new calculation level. It worked.
Total species worldwide =
CALCULATE(
CALCULATE (
DISTINCTCOUNT( Taxonomy[id] ) ,
FILTER( Taxonomy ,
Taxonomy[Rarity] <> 0
&& Taxonomy[Rarity] <> 4
&& Taxonomy[Status] = "Native"
&& Taxonomy[Type] = "S")
) ,
REMOVEFILTER ( Data[user] )
)
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |