Forum Discussion

benoit_chaceau's avatar
benoit_chaceau
Frequent Visitor
7 years ago
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 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.

  • Anonymous's avatar
    Anonymous
    7 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

  • Anonymous's avatar
    Anonymous
    Not 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_chaceau's avatar
      benoit_chaceau
      Frequent 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,