Rank based on columns conditions and rows [in measure]

[RanjitGupta]

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. 

 

[v-yinliw-msft · Accepted solution]

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)

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]

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"

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

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 )
    )