Translate SQL Query to DAX

[Anonymous]

Hello I have an SQL query as follows :

SELECT a.School, SUM(a.Value) AS Value
FROM(
select School,max(Value) AS Value from table
group by FileId,School

) a
group by School

How do I translate this into DAX ?

[Anonymous]

@Anonymous Try something like this:

Measure=var tempTable=ADDCOLUMNS(SUMMARIZE('Table','Table'[FileId],'Table'[School]),"max_value",CALCULATE(MAX('Table'[Value])))

return ADDCOLUMNS(SUMMARIZE(tempTable,[School]),"sum_value",CALCULATE(SUMX(tempTable,[max_value])))

[Anonymous]

Hello, I have created a table graph with 2 columns School and the measure you created. I received the following error :

[Anonymous · Accepted solution]

@AnonymousIt's because measure returns table (just like your SQL query). To return single value change measure:

Measure=var tempTable=ADDCOLUMNS(SUMMARIZE('Table','Table'[FileId],'Table'[School]),"max_value",CALCULATE(MAX('Table'[Value])))

return CALCULATE(SUMX(tempTable,[max_value]))

[Anonymous]

The measure worked perfectly, but can you please show me how to add a WHERE clause in it ?

SELECT a.School, SUM(a.Value) AS Value
FROM(
select School,max(Value) AS Value from table

-- New

WHERE Statut LIKE '%abc%'

--End New
group by FileId,School

) a
group by School

Thanks a lot man you are a life saver

[Anonymous]

@Anonymous Replace first parameter of SUMMARIZE function ('Table') with FILTER('Table',SEARCH("abc",'Table'[Statut],1,0)) :

Measure=var tempTable=ADDCOLUMNS(SUMMARIZE(  FILTER('Table',SEARCH("abc",'Table'[Statut],1,0)),'Table'[FileId],'Table'[School]),"max_value",CALCULATE(MAX('Table'[Value])))

return CALCULATE(SUMX(tempTable,[max_value]))