Forum Discussion

BSM1985's avatar
BSM1985
Helper IV
5 years ago
Solved

Calculations

Hi Team, I would need your help in creating the below 2 calculations using DAX.   Calculation1: Measure SUM( CASE WHEN [COLUMN1] = [COLUMN2] AND [COLUMN3] = 'A' THEN 1 END for [COLUMN4 ])   ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    BSM1985 , 

    Second one is not clear. Try like

     

    New column
    sumx(filter(Table, COLUMN4 = earlier([COLUMN4])),if( [COLUMN1] = [COLUMN2] AND [COLUMN3] = "A",1,0))

    Maxx(filter(Table, COLUMN2 = earlier([COLUMN2])),COLUMN1) -Minx(filter(Table, COLUMN2 = earlier([COLUMN2])),COLUMN1)

     

    New measure

    sumx(filter(allselected(Table), Table[COLUMN4] = max(Table[COLUMN4])),if( Table[COLUMN1] = Table[COLUMN2] AND Table[COLUMN3] = "A",1,0))

    Maxx(filter(allselected(Table), Table[COLUMN2] = max(Table[COLUMN2])),Table[COLUMN1]) -Minx(filter(Table, Table[COLUMN2] = max(Table[COLUMN2])),Table[COLUMN1])

     

    See if my Dax Vs SQL Series can help: https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj

amitchandak's avatar
amitchandak
Super User
5 years ago

BSM1985 , Try like

new columns

COLUMN4 = Sumx(Table , Table[COLUMN1] = Table[COLUMN2] AND Table[COLUMN3] = "A",1,blank())

COLUMN1 = MAx(Table[COLUMN1], Table[COLUMN2])

  • BSM1985's avatar
    BSM1985
    Helper IV
    5 years ago

    Thanks amitchandak  for your reply.

     

    Soryy I was not clear. Below are the calculations in SQL terms:

     

    calculation1:

    SELECT
    Sum(CASE WHEN [COLUMN1] = [COLUMN2] AND [COLUMN3] = 'A' THEN 1 END) OVER (partition BY COLUMN4) AS CALCULATION1
    FROM TABLE

     

    Calculation2:
    SELECT
    Min(COLUMN1)= Max(Max(COLUMN1)) OVER ( partition BY COLUMN2) AS CALCULATION2
    FROM TABLE

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      BSM1985 , 

      Second one is not clear. Try like

       

      New column
      sumx(filter(Table, COLUMN4 = earlier([COLUMN4])),if( [COLUMN1] = [COLUMN2] AND [COLUMN3] = "A",1,0))

      Maxx(filter(Table, COLUMN2 = earlier([COLUMN2])),COLUMN1) -Minx(filter(Table, COLUMN2 = earlier([COLUMN2])),COLUMN1)

       

      New measure

      sumx(filter(allselected(Table), Table[COLUMN4] = max(Table[COLUMN4])),if( Table[COLUMN1] = Table[COLUMN2] AND Table[COLUMN3] = "A",1,0))

      Maxx(filter(allselected(Table), Table[COLUMN2] = max(Table[COLUMN2])),Table[COLUMN1]) -Minx(filter(Table, Table[COLUMN2] = max(Table[COLUMN2])),Table[COLUMN1])

       

      See if my Dax Vs SQL Series can help: https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj

      • BSM1985's avatar
        BSM1985
        Helper IV
        5 years ago

        Hi Amit amitchandak ,

         

        Thanks much for this. This is what I was looking for.

        I will definitely go thru your Youtube seris on DAX vs SQL.

         

        Cheers.