Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |