Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have three columns category,category1,category2 in my dataset.
how do I create a parameter to change x-axis dynamically
When i select the category, the values should be based on category, if category 1 then values should be based on category 1
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
One sample for your reference, please check the following steps as below.
1. dupilicate the fact table, then tranpose and unpivot the table as below. M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcipFQAgDETBXlZjAqGavAiO/muAHzNiIrTUtOxzwJQttLnOwag73ODA6y7nHExlPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [category = _t, category.1 = _t, category.2 = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"category", type text}, {"category.1", type text}, {"category.2", type text}, {"value", Int64.Type}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "value")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "category type"}, {"Value", "category"}}) in #"Renamed Columns"
2. Create relationship between tables as below.
3. To create a measure as below to make the goal achieved.
Measure = VAR c = CALCULATE ( SUM ( 'fact table'[value] ), USERELATIONSHIP ( 'fact table'[category], 'All'[category] ) ) VAR c1 = CALCULATE ( SUM ( 'fact table'[value] ), USERELATIONSHIP ( 'fact table'[category.1], 'All'[category] ) ) VAR c2 = CALCULATE ( SUM ( 'fact table'[value] ), USERELATIONSHIP ( 'fact table'[category.2], 'All'[category] ) ) VAR sele = SELECTEDVALUE ( 'All'[category type] ) RETURN IF ( ISBLANK ( sele ), BLANK (), SWITCH ( sele, "category", c, "category.1", c1, "category.2", c2 ) )
Hi @Anonymous ,
One sample for your reference, please check the following steps as below.
1. dupilicate the fact table, then tranpose and unpivot the table as below. M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcipFQAgDETBXlZjAqGavAiO/muAHzNiIrTUtOxzwJQttLnOwag73ODA6y7nHExlPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [category = _t, category.1 = _t, category.2 = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"category", type text}, {"category.1", type text}, {"category.2", type text}, {"value", Int64.Type}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "value")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "category type"}, {"Value", "category"}}) in #"Renamed Columns"
2. Create relationship between tables as below.
3. To create a measure as below to make the goal achieved.
Measure = VAR c = CALCULATE ( SUM ( 'fact table'[value] ), USERELATIONSHIP ( 'fact table'[category], 'All'[category] ) ) VAR c1 = CALCULATE ( SUM ( 'fact table'[value] ), USERELATIONSHIP ( 'fact table'[category.1], 'All'[category] ) ) VAR c2 = CALCULATE ( SUM ( 'fact table'[value] ), USERELATIONSHIP ( 'fact table'[category.2], 'All'[category] ) ) VAR sele = SELECTEDVALUE ( 'All'[category type] ) RETURN IF ( ISBLANK ( sele ), BLANK (), SWITCH ( sele, "category", c, "category.1", c1, "category.2", c2 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |