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 in DAX that I visualize by week that shows the performance in %:
Clear (%) =
VAR CL =
CALCULATE (
DISTINCTCOUNT ( 'Data - InboundDeliveryRows'[Row_ID] ),
'Data - InboundDeliveryRows'[Clear] = 1,
‘Data - InboundDeliveryRows'[ID_GoodsReceiptArrivalDate] <> -1
|| ‘Data - InboundDeliveryRows'[ID_GoodsReceiptArrivalDate] <> -2,
FILTER (
‘Data - InboundDeliveryRows',
‘Data - InboundDeliveryRows'[ordersortingnumber] = ‘Data - InboundDeliveryRows'[OrdernumberOrderRowNumberCount]
)
)
VAR TOT =
CALCULATE (
COUNT ( ‘Data - InboundDeliveryRows'[Row_ID] ),
FILTER (
‘Data - InboundDeliveryRows',
‘Data - InboundDeliveryRows'[ordersortingnumber] = ‘Data - InboundDeliveryRows'[OrdernumberOrderRowNumberCount]
)
)
RETURN
CL / TOT
This measure shows 0% (correctly) if the outcome is 0% but it also shows 0% if the outcome is blank. Could you please help me adjust this measure so that if the outcome is blank I want to show blank for that week and only 0% if the actual outcome is 0%.
Which means, if no InboundDeliveryRows for one week then show blank.
I would like to go from this:
To this (if the outcome is actually blank):
Many thanks!
hi, @mrclay82
try to change your return part as below
measure=
return
if(
DISTINCTCOUNT ( 'Data - InboundDeliveryRows'[Row_ID] )=blank(),blank(),CL/TOT
)
If this post helps, please consider accept as solution to help other members find it more quickly.
Hi @mrclay82 ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi,
Thanks for your reply. It's a really complex data model so I don't really know how to make a dummy sample out of it =/
Well, you can use the following RETURN statement instead:
RETURN
IF(ISBLANK(CL / TOT), BLANK, CL / TOT)
Please try this out and let me know if this solves your issue. Also one more advice is to use DIVIDE() instead of "/" symbol:
DIVIDE(CL, TOT)
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Hi @quantumudit,
Thanks for your quick reply!
The first solution gives me the following error message:
"syntax or semantic error at line 8, position 36 (this is where the RETURN statement is): The syntax for ',' is incorrect."
The second (DIVIDE) solution shows blank if the output is blank but now it also shows blank when the output is 0%
Do you have any suggestions here? Thanks
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 |
---|---|
39 | |
25 | |
20 | |
14 | |
8 |
User | Count |
---|---|
71 | |
48 | |
46 | |
20 | |
16 |