Forum Discussion

BradleyN1's avatar
BradleyN1
Frequent Visitor
2 years ago
Solved

Check if a column is higher or lower and return value

Hey,   I have a table with numerous columns in, but two in particular I want to check whether the figures are higher or lower on each row and then return a value. The data is being pulled via Direc...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi BradleyN1 ,

    Please try below steps:

    1. below is my test table in SQL Server

    2. get data from this table in Power BI Desktop with Direct Query connection type

     

    3. create a measure with below dax formula

    Measure =
VAR _pre =
    SELECTEDVALUE ( Test[Predicted] )
VAR _actual =
    SELECTEDVALUE ( Test[Actual] )
VAR _result =
    SWITCH (
        TRUE (),
        _actual < _pre, "Less than predicted",
        _actual = _pre, "As predicted",
        _actual > _pre, "More than predicted"
    )
RETURN
    _result

    4. add a table visual with field and measure

    Please refer the attached .pbix file

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi BradleyN1 ,

Please try below steps:

1. below is my test table in SQL Server

2. get data from this table in Power BI Desktop with Direct Query connection type

 

3. create a measure with below dax formula

Measure =
VAR _pre =
    SELECTEDVALUE ( Test[Predicted] )
VAR _actual =
    SELECTEDVALUE ( Test[Actual] )
VAR _result =
    SWITCH (
        TRUE (),
        _actual < _pre, "Less than predicted",
        _actual = _pre, "As predicted",
        _actual > _pre, "More than predicted"
    )
RETURN
    _result

4. add a table visual with field and measure

Please refer the attached .pbix file

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • BradleyN1's avatar
    BradleyN1
    Frequent Visitor
    2 years ago

    Thanks, this works ğŸ˜€

    Also devesh_gupta, yours did too! I realised there was a relationship missing that was causing it to not work, thanks both