Forum Discussion
Measure with IF OR
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
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.
8 Replies
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
)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
)Wrap each mention of Sales[Region] inside SELECTEDVALUE()
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.Hi, Anonymous ;
You could use ISINSCOPE or HASEONEVALUE function,about some links as follow:
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.
