Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I hope you can help me, I have a table like the one I show below (the real one has much more data) and I need to pivot the data when the user selects a score (the score list is in another table and it is shown in power bi as a drop down list), as you can see the data in the field_name column changes from one score to another so I can not pivot in power query because it would show columns that do not correspond to the selected score.
Any idea how to do this?
Solved! Go to Solution.
To dynamically pivot data based on the user's selection of a score in Power BI, you can use DAX to create calculated columns or measures that change based on the selected score. You'll need to use a combination of DAX functions like SWITCH or IF to handle different score scenarios. Here's a step-by-step guide:
Step 1: Create a Score Selection Parameter
Step 2: Create Calculated Columns or Measures
For example, suppose your original table is named "YourTable," and you want to pivot data based on the selected score. You can create calculated columns or measures like this:
SelectedScore = SELECTEDVALUE(ScoreParameter[Score])
PivotColumn =
SWITCH(
[SelectedScore],
"Score1", [Score1FieldName],
"Score2", [Score2FieldName],
"Score3", [Score3FieldName],
"Default Calculation" -- Add a default calculation when no score is selected or for other cases
)
SelectedScore is a measure that retrieves the selected score from the parameter or disconnected table.
PivotColumn is a calculated column (or measure, depending on your preference) that uses the SWITCH function to evaluate the selected score and return the appropriate field from your table. Replace [Score1FieldName], [Score2FieldName], etc., with the actual field names you want to use for each score.
Step 3: Create Visualizations
In a table or matrix visualization, use [PivotColumn] as the field to display the data.
In charts or other visuals, use [PivotColumn] as a measure to perform calculations specific to the selected score.
By following these steps, you can pivot your data dynamically based on the selected score without the need to pre-aggregate or transform the data in Power Query. This approach allows for flexibility and real-time analysis based on user selections.
To dynamically pivot data based on the user's selection of a score in Power BI, you can use DAX to create calculated columns or measures that change based on the selected score. You'll need to use a combination of DAX functions like SWITCH or IF to handle different score scenarios. Here's a step-by-step guide:
Step 1: Create a Score Selection Parameter
Step 2: Create Calculated Columns or Measures
For example, suppose your original table is named "YourTable," and you want to pivot data based on the selected score. You can create calculated columns or measures like this:
SelectedScore = SELECTEDVALUE(ScoreParameter[Score])
PivotColumn =
SWITCH(
[SelectedScore],
"Score1", [Score1FieldName],
"Score2", [Score2FieldName],
"Score3", [Score3FieldName],
"Default Calculation" -- Add a default calculation when no score is selected or for other cases
)
SelectedScore is a measure that retrieves the selected score from the parameter or disconnected table.
PivotColumn is a calculated column (or measure, depending on your preference) that uses the SWITCH function to evaluate the selected score and return the appropriate field from your table. Replace [Score1FieldName], [Score2FieldName], etc., with the actual field names you want to use for each score.
Step 3: Create Visualizations
In a table or matrix visualization, use [PivotColumn] as the field to display the data.
In charts or other visuals, use [PivotColumn] as a measure to perform calculations specific to the selected score.
By following these steps, you can pivot your data dynamically based on the selected score without the need to pre-aggregate or transform the data in Power Query. This approach allows for flexibility and real-time analysis based on user selections.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |