Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a Data table (UnitCounts), and a Dates table. I have a measure called _avg which averages unit counts by date. I have another measure meant to return the difference of the average in the current date from the average from the previous date. It looks like this:
_range = ABS(
CALCULATE( [_avg], OFFSET(-1, , ORDERBY( Dates[Date])) )
-
[_avg]
)
How do I modify the measure to IGNORE rows where either the _avg is BLANK, or the _avg from the previous row/date (given by the OFFSET function) is BLANK? The default behavior seems to be to treat those values as zero and return a value. I want it to return blank in those scenarios. More detail in the screenshot below, and a PBIX is attached also. Thank you.
https://www.icloud.com/iclouddrive/034mSDdynrXt6DEWBGSJwvfCw#imr
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @bvy ,
Please try to modify the measure _range:
_range =
var _a = ABS(CALCULATE([_avg], OFFSET(-1, , ORDERBY(Dates[Date]))) - [_avg])
return IF(ISBLANK([_avg])||ISBLANK([_prevAvg]),BLANK(),_a)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-jianboli-msft Hey that works really well, thank you. Except for one problem. When I publish to the Service, I get an unspecified error from that measure when it's tied to a period slicer that I use. See below. This is from my actual production solution, but the measure shown is equivalent to _rangeAvg in the solution here. Works fine in Desktop. And it works fine in the Service if I eliminate the OFFSET function from the underlying _range measure calculation. Makes me think there's something buggy about it since it's a new addition to DAX (Dec. 2022).
Hi @bvy ,
This issue has been submitted internally.
Please be patient to wait for fixing. If there is any news, I will update it here.
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-jianboli-msft Thank you so much! Is there a case or ticket number you can share with me? This is pretty important for our project, even though we have a workaround for now...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |