Forum Discussion

Italian8's avatar
Italian8
Frequent Visitor
1 year ago
Solved

2 separate Calculations in 1 Measure

Is there a way to create 1 measure that contains 2 seperate calculcations that i based on a condition? the condition being a date range or if column equal certain Value? 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Italian8,

    Thanks for the reply from bhanu_gautam.

     

    The reason you need to aggregate when using columns directly in a measure is that the engine doesn’t know how to handle multiple rows or entries in a column without an explicit aggregation, which is the way DAX works.

     

    Actually, measures are context-sensitive, and aggregations like MAX, MIN, SUM, etc., help define how to handle multiple values. 

     

    As a workaround, you could consider SELECTEDVALUE(), which returns the value from the current context:

    Measure = IF(SELECTEDVALUE(Table[DateColumn]) < TODAY(), 1, 0)

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Italian8 Yes you can use if or variable

DAX
Measure =
IF(
[DateColumn] >= DATE(2023, 1, 1) && [DateColumn] <= DATE(2023, 12, 31),
CALCULATE(
SUM(Table[ValueColumn]),
Table[ConditionColumn] = "Condition1"
),
CALCULATE(
SUM(Table[ValueColumn]),
Table[ConditionColumn] = "Condition2"
)
)

Italian8's avatar
Italian8
Frequent Visitor
1 year ago

I tried to do an If statement inside a measure variable and when I start it out measure =if([DateColumn] it wont let me use my Date Column to start my if statement. i would have to use an aggerate function like MIN/Max in order to use it and i dont want to use that. Same thing with my condition column. it doesnt let me use it in a measure unless I somehow aggerate it.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Italian8,

    Thanks for the reply from bhanu_gautam.

     

    The reason you need to aggregate when using columns directly in a measure is that the engine doesn’t know how to handle multiple rows or entries in a column without an explicit aggregation, which is the way DAX works.

     

    Actually, measures are context-sensitive, and aggregations like MAX, MIN, SUM, etc., help define how to handle multiple values. 

     

    As a workaround, you could consider SELECTEDVALUE(), which returns the value from the current context:

    Measure = IF(SELECTEDVALUE(Table[DateColumn]) < TODAY(), 1, 0)

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!