Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Please can someone show me how to write the following IF measure.
I have an example where I have created a table in Power BI that looks similar to the below. I have calculated a 'YTD' field and a 'YTD' Mar 20 field. The table has a date slicer that is selecting August 2020.
I would like to create the 3rd column in the table to look like 'YTD New'. However I can't seem to work this out. The logic of the measure I want is:
YTD New = If Name = "Cash" then 'YTD Mar 20' otherwise 'YTD'
Name | YTD | YTD Mar 20 | YTD New |
Receipts from customers | 400 | 350 | 400 |
Payments to suppliers | 300 | 250 | 300 |
Interest paid | 50 | 25 | 50 |
Cash | 100 | 40 | 40 |
The other 2 columns are calculated by:
Calculated column:
Solved! Go to Solution.
Try this measure:
YTD New = IF ( MAX ( Table1[Name] ) = "Cash", [YTD Mar 2020], MAX ( Table1[YTD] ) )
Proud to be a Super User!
Hi @SonyT01 ,
According to your description and my previous experience, I guess you seem to mix measures and calculated columns. It does not meet the definition rules of DAX functions.
This error can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Result:
I think you can modify the "YTD NEW" measure appropriately as follows.
YTD NEW =
var min_value=MIN('Table 1'[Name])
return
IF(
min_value="cash",
'Table 1'[YTD Mar 2020],
MIN('Table 1'[YTD])
)
Result:
I hope my suggestion can give you some help.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much!! I have been trying to figure this out with no success. This has achieved the result I was after.
Many thanks again!
Hi @SonyT01 ,
According to your description and my previous experience, I guess you seem to mix measures and calculated columns. It does not meet the definition rules of DAX functions.
This error can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Result:
I think you can modify the "YTD NEW" measure appropriately as follows.
YTD NEW =
var min_value=MIN('Table 1'[Name])
return
IF(
min_value="cash",
'Table 1'[YTD Mar 2020],
MIN('Table 1'[YTD])
)
Result:
I hope my suggestion can give you some help.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much!! I have been trying to figure this out with no success. This has achieved the result I was after.
Many thanks again!
Try this measure:
YTD New = IF ( MAX ( Table1[Name] ) = "Cash", [YTD Mar 2020], MAX ( Table1[YTD] ) )
Proud to be a Super User!
Thank you for your help. It did work well for each category, but not for the total amount.
I was trying to calculate a % of accomplishment for each category, but the Total % achievement it just sums each category, it doesn't calculate the correct amount.
Do you know how can i fix it?
User | Count |
---|---|
89 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |