Measure with IF OR

[Anonymous]

Hi All,

I am trying to create a measure based below condition

I have Sales and Cost Measure,I need to calculate margin based on below

IF (Sales[Region] in {"FPT","BRT","ZXT"}|| LEFT(Sales[Region],3)="FRT",Sales[SalesAmt],Sales[SalesAmt]-Sales[Margin])

I get the single table error,I tried calulated column but it errors out as soon as i add it to the report.I am ASS bulit models.

Regards,

Sri

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Try to create a measure.

Result =
VAR a =
    SUM ( Sales[SalesAmt] )
RETURN
    IF (
        MAX ( Sales[Region] )
            IN { "FPT", "BRT", "ZXT" }
                || LEFT ( MAX ( Sales[Region] ), 3 ) = "FRT",
        a,
        a - SUM ( Sales[Margin] )
    )

If not right, can you share a simple example so that we can better test it?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could use ISINSCOPE or HASEONEVALUE function,about some links as follow:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Display-Values-without-including-in-calculation/m-p/2420231#M64078

https://docs.microsoft.com/en-us/dax/hasonevalue-function-dax

Or you could provide a simple file.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is perfect except for Grand Total in table view...it show Row level total instead of column level...

[truptis]

This usually happens 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. @Anonymous 
Try this:
Result =
var value1 = LEFT(values(Sales[Region],3))
var a = sum(Sales[SalesAmt])
var b = sum(Sales[SalesAmt])-sum(Sales[Margin])
RETURN
IF (
values(Sales[Region]) in {"FPT","BRT","ZXT"}
|| value1 in {"FRT"},
a, b
)

[Anonymous]

When i create calulcated column .. i get circular dependency error ....

[truptis]

Hi @Anonymous , Try this:
Result =
var value1 = LEFT(Sales[Region],3)
var a = sum(Sales[SalesAmt])
var b = sum(Sales[SalesAmt])-sum(Sales[Margin])
RETURN
IF (
Sales[Region] in {"FPT","BRT","ZXT"}
|| value1 in {"FRT"},
a, b
)

[Anonymous]

I get error single value for Region in table Sales cannot be determined.

[johnt75]

Wrap each mention of Sales[Region] inside SELECTEDVALUE()