Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I wish to calculate free capacity which is math difference between 100% and Loading, %.
[Loading, %] - is a measure which calculated from inputs file as:
Solved! Go to Solution.
Does this work better?
Free Capacity, % =
VAR Loading = [Loading, %]
RETURN
IF (
ISBLANK ( Loading ),
BLANK (),
MAX ( 1 - Loading, 0 )
)
In order to make it work I replaced all nulls with "0" in query (inputs file). It, unfortunatelly increased my dataset, but at least I have now only zeros (no empty) and it works fine.
Thanks!
I modefied IF function as per my needs and its works, solution is excepted, thanks a lot!!
Now need to understand what was wrong with mine 🙂
Your original measure always returned a value regardless of date since if [Loading, %] were blank, it would still return 1 - BLANK() = 1.
My measure specified that it should only return something if [Loading, %] is not blank.
and if I need to return 100% if Loading, % is zero or blank in inputs? i'm asking because equation is
Loading, % + Free Capacity, % = 100%
What I suggested should work fine for 0%.
If it's blank, then things are more difficult because there's no way to tell from a blank why it's blank (and you apparently want to treat certain cases of blank differently).
If there are some blank values that you want to interpret as zero, then you'll need to build in logic to specify why those blanks are different from other blanks. This is possible but I don't have sufficient context to write the appropriate logic for you.
Does this work better?
Free Capacity, % =
VAR Loading = [Loading, %]
RETURN
IF (
ISBLANK ( Loading ),
BLANK (),
MAX ( 1 - Loading, 0 )
)
User | Count |
---|---|
15 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
31 | |
18 | |
13 | |
7 | |
5 |