Dynamic Pivot with DAX

[gianstanco]

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?

[123abc · Accepted 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

1. Create a parameter or a disconnected table in Power BI that allows users to select a score. This parameter should contain the available score options.

Step 2: Create Calculated Columns or Measures

1. Create calculated columns or measures that depend on the selected score. You'll need to use conditional logic to determine which calculation to perform based on the selected score.

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

1. Use the PivotColumn calculated column or measure in your visualizations to display the data dynamically based on the selected score.

• 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.