Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
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 )
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |