Forum Discussion

Reawaken's avatar
Reawaken
Regular Visitor
3 years ago
Solved

Add up values in one column based on whether a value in another column is of a specific value

Hi there,

 

I am trying to add up values in a column based on whether a value in another column has a value of 4 or 5.

 

What I'd like to be able to do, from the information in the pictur above, is scan through the NEW CRATE column, and if the value is a "4" or "5", I want to add that value from the BALANCE column and place it in a new variable or that I can use in a division equation.

 

I'd like to see the value of "21400" stored in a new value and then later used for downstream DAX functions.  What I have so far is this:

CALCULATE(
    VALUE(ALL(Equifax[New Crate], Equifax[New Crate] = MAX(Equifax[New Crate] = 4 OR 5)))
)
 
I'm learning DAX for work and need some help with this; not as straight forward as python code, for e.g.
 
Thanks!

 

 

Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Write this measure and drag it to a card visual

    Measure = calculate(sum(Data[Balance]),data[new crate]=4||data[new crate]=5)

    Hope this helps.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Reawaken , In case you need a new column

     

    New column = if( Equifax[New Crate] in {4,5} , [Balance], blank())

     

    • Reawaken's avatar
      Reawaken
      Regular Visitor
      3 years ago

      Hi Amit,

       

      You're right that my table is called Equifax, with dimenions Balance, New Crate, etc., but I dont think your expression is working cause I don't see the drop down to select "Equifax[New Crate]" after the "IF" statement.  Help!  I'm a newbie at this haha.

       

      Thanks!

       

      Paul

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Write this measure and drag it to a card visual

    Measure = calculate(sum(Data[Balance]),data[new crate]=4||data[new crate]=5)

    Hope this helps.

    • Reawaken's avatar
      Reawaken
      Regular Visitor
      3 years ago

      Hi Ashish,

       

      Can you help me understand the "||" and how this is used properly?  I cant seem to finish the rest of your DAX expression cause I'm getting an error message, "The following syntax error occurred during parsing: Invalid toekn, Line 1, Offset 52, |E".  The "E" is the first letter of the table of data I'm using.  Might be good to mention that both "Balance" and "New Crate" are in the same table.

       

      Thanks for your help, Ashish!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        You are welcome.  || means OR.  Write my formula as a measure.