Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RanjitGupta
New Member

Rank based on columns conditions and rows [in measure]

Hi,

 

Thankyou for the help in advance. 

 

I am trying to create a measure which will rank my set of names based on the rank in columns. I have attached an image as example. In the final output the rank should be prioritized by the rows starting A, B,C,D,E and so on. 

 

 

 

 

 unnamed.jpg

1 ACCEPTED SOLUTION
v-yinliw-msft
Community Support
Community Support

Hi @RanjitGupta ,

 

You can try this method.

 

New table:

Table 2 = Var _Table = UNION(



    SUMMARIZE('Table','Table'[Type],'Table'[A]),



    SUMMARIZE('Table','Table'[Type],'Table'[B]),



    SUMMARIZE('Table','Table'[Type],'Table'[C]),



    SUMMARIZE('Table','Table'[Type],'Table'[D]),



    SUMMARIZE('Table','Table'[Type],'Table'[E]),



    SUMMARIZE('Table','Table'[Type],'Table'[F]))



return



DISTINCT(FILTER(_Table,[A]="1"))

Measure:

Measure = IF(SELECTEDVALUE('Table'[Type]) in VALUES('Table 2'[Type]),1,0)

vyinliwmsft_0-1664348798295.png

 

 

Is this the result you would expect?

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yinliw-msft
Community Support
Community Support

Hi @RanjitGupta ,

 

You can try this method.

 

New table:

Table 2 = Var _Table = UNION(



    SUMMARIZE('Table','Table'[Type],'Table'[A]),



    SUMMARIZE('Table','Table'[Type],'Table'[B]),



    SUMMARIZE('Table','Table'[Type],'Table'[C]),



    SUMMARIZE('Table','Table'[Type],'Table'[D]),



    SUMMARIZE('Table','Table'[Type],'Table'[E]),



    SUMMARIZE('Table','Table'[Type],'Table'[F]))



return



DISTINCT(FILTER(_Table,[A]="1"))

Measure:

Measure = IF(SELECTEDVALUE('Table'[Type]) in VALUES('Table 2'[Type]),1,0)

vyinliwmsft_0-1664348798295.png

 

 

Is this the result you would expect?

Hope this helps you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

I suggest having a table structure transformed like below in Power Query Editor.

 

You can also see the below applied-steps by opening the attached pbix file -> go to Power Query Editor.

 

Power Query advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyBGIzJNoIiM2VYnWilZwSi7NTS+BCCNoEzAYpcU4EyRtDtRtD1RiDMUjeJT8drh5CG0KxCVjeLbM4A8gxhWpCpk0hFuSXQ91lgkabgeV980uLU6HOMkWjDZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Category"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Category"}, {{"IndexAdd", each Table.AddIndexColumn( _, "Index",1,1)}}),
    #"Expanded IndexAdd" = Table.ExpandTableColumn(#"Grouped Rows", "IndexAdd", {"Attribute", "Value", "Index"}, {"Attribute", "Value", "Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded IndexAdd",{{"Category", type text}, {"Attribute", type text}, {"Value", Int64.Type}, {"Index", Int64.Type}})
in
    #"Changed Type"

 

 

Jihwan_Kim_1-1664344437335.png

 

 

And then, load it and try to create a measure and a table visualization like below.

 

Jihwan_Kim_0-1664344401779.png

 

DAX measure:

Final output measure: =
VAR _currentindex =
    MAX ( Data[Index] )
VAR _newtable =
    FILTER (
        ADDCOLUMNS (
            ALL ( Data[Attribute], Data[Index] ),
            "@rankonecategory", CALCULATE ( MAXX ( FILTER ( Data, Data[Value] = 1 ), Data[Category] ) )
        ),
        Data[Index] < _currentindex
    )
VAR _prevrankonecategorylist =
    SUMMARIZE ( _newtable, [@rankonecategory] )
VAR _result =
    MAXX ( FILTER ( Data, Data[Value] = 1 ), Data[Category] )
RETURN
    IF (
        HASONEVALUE ( Data[Attribute] ),
        IF ( _result IN _prevrankonecategorylist, BLANK (), _result )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors