Forum Discussion
Solved
Help with SQL to DAX (subquery select from select)
Hello, I am having difficulty translating this SQL query with a subquery (SELECT FROM SELECT) to DAX: SELECT Kids.[age]
,Kids.[number]
,Kids.[rank_age]
FROM (
SELECT ROW_NUMBER() OVER(ORDER...
HI benoit_chaceau,
You can try to use below formula if it suitable for your requirement:
Kids = VAR summary = SUMMARIZE ( FILTER ( ALL ( School ), [hair_color] IN { 3, 4 } && [age] IN { 1, 2, 3, 4, 5 } ), [Age], "number", COUNT ( School[id_kid] ) ) RETURN FILTER ( ADDCOLUMNS ( summary, "Rank", RANKX ( summary, [number],, DESC, DENSE ) ), [age] = 4 )If above not help, please share some sample data for test.
Regards,
Xiaoxin Sheng
HI benoit_chaceau,
You can try to use below formula if it suitable for your requirement:
Kids =
VAR summary =
SUMMARIZE (
FILTER (
ALL ( School ),
[hair_color] IN { 3, 4 }
&& [age] IN { 1, 2, 3, 4, 5 }
),
[Age],
"number", COUNT ( School[id_kid] )
)
RETURN
FILTER (
ADDCOLUMNS ( summary, "Rank", RANKX ( summary, [number],, DESC, DENSE ) ),
[age] = 4
)
If above not help, please share some sample data for test.
Regards,
Xiaoxin Sheng
Hello Anonymous
Thank you for your answer, it worked!
Now I have a better understanding of the SUMMARIZE function, thank you so much.
Regards,
