Forum Discussion
Showing specific columns respective to Rule_ID(Dynamic change in table visualization)
Hi Rahul_001 ,
As DataNinja777 mentioned before, Field parameter is a good way. However it needs us to choose the column name manually after we filter Rule ID.
As far as I know, I suggest you to use matrix, because it is the best way to hide and show columns dynamiclly.
If you still need to create table visuals, you can try this way.
Add an Index column in 'Exception Data' table. Then create five measures for these columns.
Customer_ID = VAR _Indexlist = VALUES(Excp_Col_Metadata[Excp_col_index]) RETURN IF(1 IN _Indexlist,CALCULATE(SUM('Exception Data'[Excp_Col_Value_1])))Name = VAR _Indexlist = VALUES(Excp_Col_Metadata[Excp_col_index]) RETURN IF(2 IN _Indexlist,CALCULATE(MAX('Exception Data'[Excp_Col_Value_2])))...
Result is as below.
We can find that it couldn't hide the column header for the empty column.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rahul_001 ,
I suggest you to try unpivot function in Exception Data table.
Unpivot columns - Power Query | Microsoft Learn
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZGxDoIwEED/pTMYWkUd3QhGjIluhOECRBqkJNAa8OvlcqSGwUHTpe+uw3tJm6aMBwFnHuOhfwTli0CEuHFOJ+IMndGtHQ73BuRjlbfNdHUxqpwQq0LCRLGwDOOLZd7XhKAE4gq9qUs7OEusKYGINeSVtIOzxIYSiAQK6MAOzhLzjyAiCkT/6unNl/rtR5/AMMxwpt+RHisn2Y8znOn3pMfKDfQIT6nyyiy3H2PZGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rule_ID = _t, Rule_Run_Date = _t, EXCP_ID = _t, Excp_Col_Value_1 = _t, Excp_Col_Value_2 = _t, Excp_Col_Value_3 = _t, Excp_Col_Value_4 = _t, Excp_Col_Value_5 = _t, Updated_Timestamp = _t, Updated_by = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rule_ID", Int64.Type}, {"Rule_Run_Date", type date}, {"EXCP_ID", Int64.Type}, {"Excp_Col_Value_1", Int64.Type}, {"Excp_Col_Value_2", type text}, {"Excp_Col_Value_3", type text}, {"Excp_Col_Value_4", type text}, {"Excp_Col_Value_5", type text}, {"Updated_Timestamp", type date}, {"Updated_by", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "Data_Index"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Rule_ID", "Rule_Run_Date", "EXCP_ID", "Updated_Timestamp", "Updated_by", "Data_Index"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Excp_Col_Value_","",Replacer.ReplaceText,{"Attribute"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}, {"Value", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute", "Excp_col_index"}})
in
#"Renamed Columns"New Table looks like as below.
Then create [Keyword] column by dax in two tables.
KeyWord = 'Exception Data'[Rule_ID] * 100 + 'Exception Data'[Excp_col_index]KeyWord = Excp_Col_Metadata[Rule_ID] * 100 + Excp_Col_Metadata[Excp_col_index]Relationship:
Filter [Rule ID] not show blank.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I tried your way its too easy to implement and i achieved it, but however business wants in table visual as per their need.
Thanks,
Rahul Tatar
Hi Rahul_001 ,
As DataNinja777 mentioned before, Field parameter is a good way. However it needs us to choose the column name manually after we filter Rule ID.
As far as I know, I suggest you to use matrix, because it is the best way to hide and show columns dynamiclly.
If you still need to create table visuals, you can try this way.
Add an Index column in 'Exception Data' table. Then create five measures for these columns.
Customer_ID = VAR _Indexlist = VALUES(Excp_Col_Metadata[Excp_col_index]) RETURN IF(1 IN _Indexlist,CALCULATE(SUM('Exception Data'[Excp_Col_Value_1])))Name = VAR _Indexlist = VALUES(Excp_Col_Metadata[Excp_col_index]) RETURN IF(2 IN _Indexlist,CALCULATE(MAX('Exception Data'[Excp_Col_Value_2])))...
Result is as below.
We can find that it couldn't hide the column header for the empty column.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.