Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |