Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
)
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.
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |