Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
Solved! Go to 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 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])))
Hello, I have created a table graph with 2 columns School and the measure you created. I received the following error :
@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]))
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 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]))
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
60 | |
43 | |
40 |