Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
gianstanco
New Member

Dynamic Pivot with DAX

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?

 

gianstanco_0-1695365714391.png

 

 

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

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.

 
 
 

View solution in original post

1 REPLY 1
123abc
Community Champion
Community Champion

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.

 
 
 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.