If Statement with Column and Measure

[gmasta1129]

Hello,

I am having issues with the measure value that pulls in when i use it in an if statement with a column

The formula is as follows...

Measure =  if(daily_report[master portfolio id]=729,[Net FX P&L2],CALCULATE(SUM(daily_report[fx prv])))

Measure = Net FX P&L2

Column = Daily_report[fxprv]

Column = daily_report[master portfolio id]

When i add the Net FX P&L2 measure to the power bi table, the correct value pulls in.  The issue is when I include the measure in the "if formula", it sums all values for each day even though i have a date slicer set up on my page (set for 3/28/2022).  it should only pull in the value for 3/28/2022 but is summing all values for all dates (1/1/2022-3/28/2022).  Not sure what i am doing wrong.  

[amitchandak · Accepted solution]

@gmasta1129 , That seem more like column not measure , Try a measure like

CALCULATE(SUM(daily_report[fx prv]), filter(daily_report,daily_report[master portfolio id] <> 729))
+ CALCULATE([Net FX P&L2], filter(daily_report,daily_report[master portfolio id] = 729))

[v-stephen-msft · Accepted solution]

Hi @gmasta1129 ,

Your formula with IF function seems to be a calculated column, right? A calculated column is static, it won't be changed by the filter of the slicer. However, measures do. You could create a measure instead of the orginal calculated column.

What is the difference between Power BI calculated columns and measures? - TrueCue.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@gmasta1129 , That seem more like column not measure , Try a measure like

CALCULATE(SUM(daily_report[fx prv]), filter(daily_report,daily_report[master portfolio id] <> 729))
+ CALCULATE([Net FX P&L2], filter(daily_report,daily_report[master portfolio id] = 729))