Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I hope someone can help me with the following problem:
I have two columns from a calculated table.
I'd like to have a third calculated column with DAX that only takes one value % Percent from each Code, the expected table is
I've tried with creating Index to compare each row with the previouse but I don't actually have a context to create the index with.
Many thanks!
Hi @Abdel_BCN
1.You can handle it in power query.
Put the following code to Advanced Editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijA0VNJRMjbRM7BQVYrVIU7ACChgaGqgZ25AiogxSMTQVM/QgkiRWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, #"Percent%" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Percent%", Percentage.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Count", each _, type table [Code=nullable text, #"Percent%"=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Code", "Percent%", "Rank"}, {"Code.1", "Percent%", "Rank"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [Rank] = 1 then [#"Percent%"] else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Percentage.Type}, {"Rank", Int64.Type}})
in
#"Changed Type1"
Output
2.You can create a index column in power query
Then create a calculated column
Column = var a=COUNTROWS(FILTER('Table (2)',[Code]<>EARLIER('Table (2)'[Code])&&[Index]<=EARLIER('Table (2)'[Index])))
return IF([Index]-a=1,[Percent%])
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xinruzhu-msft ,
thanks for your reply and the effort you made.
the thing is that this table is a calculated table and can't work with it using power query.
not sure if there is away on adding the index column using DAX.
Many thanks,
my best regards
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |