Calculations

[BSM1985]

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

])

Calculation2: Column
[COLUMN1] = maximum(COLUMN1 for COLUMN2)

[Ashish_Mathur]

Hi,

Explain the question in simple English and on a dummy dataset show the expected result.

[amitchandak]

@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]

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 · Accepted solution]

@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]

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.