Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have built a simple DAX ratio using DistintCount, which works as intended. Problem is when I apply a filter that is related to the numerator. This is a simple binary filter of yes/no and thus there should be only two answers, 100% or 0% but I get wonky (technical term) results. What did I miss?
DATA:
Total Records=6346
BLUE Records=2325
Ratio=0.366 or 36.6%
DAX FORMULA:
RESULTS:
1. When I set the filter to, "Yes", I get 100%.
2. When I set the filter to, "No", I get 57.8%
What am I doing wrong? I've build a lot of DAX ratio calculations into this model that all worked fine until I started adding filters. Again, it only appears to get wonky when the filter is the numerator.
Thanks!
FILTER: Blue column of yes/no
Solved! Go to Solution.
As you have an expression filter in the numerator, this is overriding the slicer. The denominator does not have this, so the slicer is being applied to the denominator only.
Try:
% BLUE = Divide( Calculate(DISTINCTCOUNT('Center'[id]), 'Center'[BLUE]="Yes") , Calculate(DISTINCTCOUNT('Center'[id]), ALL('Center'[BLUE]) ) )
Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!
Stay up to date on
Read my blogs on
Remember to spread knowledge in the community when you can!
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
As you have an expression filter in the numerator, this is overriding the slicer. The denominator does not have this, so the slicer is being applied to the denominator only.
Try:
% BLUE = Divide( Calculate(DISTINCTCOUNT('Center'[id]), 'Center'[BLUE]="Yes") , Calculate(DISTINCTCOUNT('Center'[id]), ALL('Center'[BLUE]) ) )
Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!
Stay up to date on
Read my blogs on
Remember to spread knowledge in the community when you can!
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
That seems to fix the results and the filter has no impact. Here is another DAX with the same weird results with gender identity. I've tried it on another measure for gender identity and get the same fixed ratio.
Here is another example:
% Male = Calculate(DISTINCTCOUNT('FTE by Center'[id]), 'FTE by Center'[Gender_Identify] = "Male") / DISTINCTCOUNT('FTE by Center'[id])
Maybe I have misunderstood. What is the desired result - to get 100% when yes is selcted and 0% for no?
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Exactly. However, it is more complicated than that. I have lots of demographic data on persons in a table and my customers want to slice and dice using multiple filters. I'm using a multi-row card for quick facts as customers slice their data and the results are driving me crazy. If I create a measure of distinctcount for gender="male" and filter to "female", I get a result of 0. So why doesn't the ratio calculation show 0? I'm baffled.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |