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.

devesh_gupta's avatar
devesh_gupta
Icon for Impactful Individual rankImpactful Individual
2 years ago

Try to use the following formula for creating a measure: 

=IF('TableName'[Actual]<'TableName'[Predicted],"Less than predicted",IF('TableName'[Actual]='TableName'[Predicted],"As predicted","More than predicted"))

 

If you find this insightful, please provide a Kudo and accept this as a solution.

  • BradleyN1's avatar
    BradleyN1
    Frequent Visitor
    2 years ago

    Hey there, thank you.

    It does not allow me to select the Predicted field in this IF statement, its only showing measures I have created.

     

    I thought I could add a sum(predicted) but when I do that, it just makes my whole column the same value and my other columns are all duplicated.

     

    • devesh_gupta's avatar
      devesh_gupta
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

      BradleyN1 Can you try to create a column instead of a measure with same formula so that if it'll allow you to use your column names inside the IF statement:

      ColumnName = IF('TableName'[Actual]<'TableName'[Predicted],"Less than predicted",IF('TableName'[Actual]='TableName'[Predicted],"As predicted","More than predicted"))

       

      Then you don't need to create another measure for sum(predicted). I'm not sure but try if it works and do let me know.

       

      If you find this insightful, please provide a Kudo and accept this as a solution.

      • BradleyN1's avatar
        BradleyN1
        Frequent Visitor
        2 years ago

        Hey,

        Thanks for this, it tells me 'Function 'CALCULATE' is not allowed as part of a calculated column DAX expressions on DirectQuery models'