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.
Hi,
I have a measure (Measure3) which does this
Measure3%:=IF([Measure1]=0, BLANK(),[Measure2]/[Measure1])
Measure1 | Measure2 | Measure3% |
0.5 | ||
0.5 | ||
1 |
I want Measure3% to do this
Measure1 | Measure2 | Measure3% | Measure3% DESIRED |
0.5 | 0.00% | ||
N/A | |||
0.5 | 0.00% | ||
N/A | |||
1 | 0.00% |
Please let me know how I can make Measure3% to also show
0% --> If Measure1 has a value and Measure2 is BLANK
N/A --> If Measure1 and Measure2 is BLANK
using my current DAX formula for Measure3%
Thanks
hi,
try this:
Measure3% :=
IF (
AND ( ISBLANK ( [Measure1] ), ISBLANK ( [Measure2] ) ),
"N/A",
DIVIDE ( [Measure2], [Measure1], BLANK () )
)
radpir
Hi radpir
Thanks for the quick response but its not working
sorry, my bad.
both sides of the IF function needs to return the same data type.
N/A is a text value and DIVIDE returns a numeric value hence the reason why this formula doesn't work.
this one should work:
Measure3 = IF (
AND ( ISBLANK ( [Measure1] ), ISBLANK ( [Measure2] ) ),
"N/A",
FORMAT(DIVIDE ( [Measure2], [Measure1], BLANK () ), "0.00")
)
I wrapped the DIVIDE function in the FORMAT function which returns a text value.
The one big drawback of this approach is that this measure is not returning text values even though they may appear as numeric values. They can't be summed, averaged, etc.
radpir
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |