Forum Discussion
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 BY COUNT([id_kid]) DESC) AS rank_age
,[age]
,COUNT([id]) AS number
FROM [dbo].[School]
WHERE [hair_color] in (3,4)
AND [age] in (1,2,3,4,5)
GROUP BY [age]
) as Kids
WHERE Kids.[age] = 4
What I am trying to do is to sort kids by age, count them, and establish a ranking.
I am using a subquery to be able to get the exact rank for certain age (for example 4) of the kids.
This is how I create the measure 'rank_age' in Power BI:
rank_age = RANKX ( ALLSELECTED ( Kids[age]) ; [number] )
And this is what I get by filtering on the age (1, 2, 3, 4 and 5):
What I now want to do is to use another filter to display only some of the ages, but maintaining the right ranks (the reason I'm using a subquery in the SQL script).
The result would be something like this (if I filter on the ages of 2 and 4):
I don't know which function to use in order to get the above result. Maybe it just can't be done with such method...
Any help would be very appreciated!
Thank you.
- Anonymous7 years ago
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
2 Replies
- AnonymousNot applicable
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
- benoit_chaceauFrequent Visitor
Hello Anonymous
Thank you for your answer, it worked!
Now I have a better understanding of the SUMMARIZE function, thank you so much.
Regards,