Get a List of Member of Categories

[angsoka1717]

Dear all,

I need your help. I have a table consisting of three columns. Country name, growth of export, and growth of import. Then I create a measure to categorize based on the value of those two columns (first quadrant, second quadrant, third quadrant, and fourth quadrant).  

Name	Export Growth	Import Growth	Quadrant
Indonesia	+1.0%	+2.0%	Quadrant I
Thailand	-5.0%	-1.0%	Quadrant III
Singapore	-3.0%	+5.0%	Quadrant IV
Philippines	+8.0%	+6.0%	Quadrant I
Malaysia	-2.0%	+7.0%	Quadrant IV
Vietnam	+4.0%	+1.0%	Quadrant I

Quadrant:=SWITCH(TRUE,

[growth export] > 0 && [growth import] > 0, "Quadrant I",

[growth export] > 0 && [growth import] < 0, "Quadrant II",

[growth export] < 0 && [growth import] < 0, "Quadrant III",

[growth export] < 0 && [growth import] > 0, "Quadrant IV")

Question :
I want to create a summary table like this.

Quadrant I	Quadrant II	Quadrant III	Quadrant IV
Indonesia		Thailand	Singapore
Philippines			Malaysia
Vietnam

Please help.

angsoka

[barritown · Accepted solution]

Hello @angsoka1717,

I can propose to solve your problem by creating a new table with the following [DAX] code:

Here is the same code in plain text:

Table = 
VAR Tbl1 = ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( data, [Quadrant] = "Quadrant I" ), "Q1", [Name] ), "Value", RANKX ( FILTER ( data, [Quadrant] = "Quadrant I" ), [Name], [Q1], ASC ) )
VAR Tbl2 = ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( data, [Quadrant] = "Quadrant II" ), "Q2", [Name] ), "Value", RANKX ( FILTER ( data, [Quadrant] = "Quadrant II" ), [Name], [Q2], ASC ) )
VAR Tbl3 = ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( data, [Quadrant] = "Quadrant III" ), "Q3", [Name] ), "Value", RANKX ( FILTER ( data, [Quadrant] = "Quadrant III" ), [Name], [Q3], ASC ) )
VAR Tbl4 = ADDCOLUMNS ( SELECTCOLUMNS ( FILTER ( data, [Quadrant] = "Quadrant IV" ), "Q4", [Name] ), "Value", RANKX ( FILTER ( data, [Quadrant] = "Quadrant IV" ), [Name], [Q4], ASC ) )
VAR MaxNum = MAXX ( { COUNTX ( FILTER ( data, data[Quadrant] = "Quadrant I" ), [Name] ),
                      COUNTX ( FILTER ( data, data[Quadrant] = "Quadrant II" ), [Name] ),
                      COUNTX ( FILTER ( data, data[Quadrant] = "Quadrant III" ), [Name] ),
                      COUNTX ( FILTER ( data, data[Quadrant] = "Quadrant IV" ), [Name] ) },
                    [Value] )
VAR Res = NATURALLEFTOUTERJOIN 
            ( NATURALLEFTOUTERJOIN 
                ( NATURALLEFTOUTERJOIN 
                    ( NATURALLEFTOUTERJOIN ( GENERATESERIES ( 1, MaxNum ), Tbl1 ),
                    Tbl2 ),
                Tbl3 ),
            Tbl4 )
RETURN SELECTCOLUMNS ( Res, "Quadrant I", [Q1], "Quadrant II", [Q2], "Quadrant III", [Q3], "Quadrant IV", [Q4] ) 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[angsoka1717]

Thank you so much.

It works in Power BI. However, I am still trying to bring that measure to Power Pivot in Excel. 

Best regards,
angsoka