Forum Discussion
Apply same dynamic formatting method for a multitude of columns in table
Community ChampionHi Anonymous
The best approach is to unpivot the columns using power query. It is possible to unpivot the data using DAX but this will require to create a new table and hence duplicate the data.
In power query editor select all the "Year Month" columns and select unpivot
Rename the columns inside the code as convenient
Add the value column as a measure - example:
KPI Value =
IF (
SELECTEDVALUE ( Table1[KPI] ) IN { 2, 3 },
FORMAT ( SELECTEDVALUE ( Table1[Value] ), "Percent" ),
SELECTEDVALUE ( Table1[Value] )
) Create your matrix: "KPI" on rows, "Year Month" on columns and the measure on values
Here is a sample file for your reference https://www.dropbox.com/t/EcbFqwYlnAqOF54F
Hi tamerj1 Thanks for the solution, it works, however it is slow for my actual dataset it takes close to half minute to refresh the matrix. Any suggestion to speed up it.? my dax is :
- tamerj1
Anonymous
Could be related to the visual rather than the code. However you can use variablesMEA = VAR PercentKPIs = VALUES ( 'KPI_PERC_F'[KPI] ) VAR SelectedKPI = SELECTEDVALUE ( 'TBL'[KPI] ) RETURN IF ( SelectedKPI IN PercentKPIs, FORMAT ( SelectedKPI, "0.0%" ), SelectedKPI )Visual part it is less than 500ms on perforamnce analyzer. variables do help, the first variable does not impove the performance a lot but the second variable SelectedKPI on your code reduce the dax query time from 75 s to 16 s. one further question: if user want to view selected column say (Mar in my table above) all in % form regardless of the row content. Any modification we can do? Thanks
- tamerj1
Please clarify further