Forum Discussion
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
])
Calculation2: Column
[COLUMN1] = maximum(COLUMN1 for COLUMN2)
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
5 Replies
BSM1985 , Try like
new columns
COLUMN4 = Sumx(Table , Table[COLUMN1] = Table[COLUMN2] AND Table[COLUMN3] = "A",1,blank())
COLUMN1 = MAx(Table[COLUMN1], Table[COLUMN2])
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 TABLECalculation2:
SELECT
Min(COLUMN1)= Max(Max(COLUMN1)) OVER ( partition BY COLUMN2) AS CALCULATION2
FROM TABLEBSM1985 ,
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
Hi,
Explain the question in simple English and on a dummy dataset show the expected result.
