topic Re: Apply same dynamic formatting method for a multitude of columns in table in DAX Commands and Tips

Apply same dynamic formatting method for a multitude of columns in table

Anonymous — Sun, 03 Apr 2022 12:20:44 GMT

I have a well prepared source table listing down individual's monthly KPI performance. The PBI report only need to show the source table. After the source table is imported, the data looks like Table 1 and we need final look like Table 2. As shown, for some of the KPIs, we need to show in % format. I have more than 80 columns (different year month) in source table and each column need to apply this formatting method. Thinking of using calculation groups in tablur editor but all the columns are columns in source table (not measure, to make a measure for each column, it is labour-intensive).

Any advice how can I quickly apply same formatting on multiple columns?

Re: Apply same dynamic formatting method for a multitude of columns in table

tamerj1 — Mon, 04 Apr 2022 02:35:33 GMT

Hi 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

Re: Apply same dynamic formatting method for a multitude of columns in table

Anonymous — Wed, 06 Apr 2022 01:38:00 GMT

Hi Anonymous ,

This feature is not currently supported by Power BI.

Please vote here: Select all columns / select more than one column for field and conditional formatting

If I have misunderstood your meaning, please provide more details and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Apply same dynamic formatting method for a multitude of columns in table

Anonymous — Thu, 05 May 2022 07:53:27 GMT

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 :

mea =

var t = 'KPI_PERC_F'[KPI]

return IF ( SELECTEDVALUE ( 'TBL'[KPI] ) IN t, FORMAT ( SELECTEDVALUE ( TBL[Value] ), "0.0%" ),

selectedvalue(TBL[Value]) )

In 'KPI_PERC_F'[KPI], there are about 250 data.

Re: Apply same dynamic formatting method for a multitude of columns in table

tamerj1 — Thu, 05 May 2022 08:10:26 GMT

Anonymous
Could be related to the visual rather than the code. However you can use variables

MEA = VAR PercentKPIs = VALUES ( 'KPI_PERC_F'[KPI] ) VAR SelectedKPI = SELECTEDVALUE ( 'TBL'[KPI] ) RETURN IF ( SelectedKPI IN PercentKPIs, FORMAT ( SelectedKPI, "0.0%" ), SelectedKPI )

Re: Apply same dynamic formatting method for a multitude of columns in table

Anonymous — Thu, 05 May 2022 08:31:08 GMT

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

Re: Apply same dynamic formatting method for a multitude of columns in table

tamerj1 — Thu, 05 May 2022 08:53:01 GMT

Please clarify further

Re: Apply same dynamic formatting method for a multitude of columns in table

Anonymous — Fri, 06 May 2022 05:26:44 GMT

I mean if we want data in selected column all in percentage format (for example 202202 and 202203 these two column, highlighted value to be shown in percentage), how can we modify the DAX. In short, the format varies with both rows and columns

Re: Apply same dynamic formatting method for a multitude of columns in table

tamerj1 — Fri, 06 May 2022 05:48:35 GMT

Anonymous
Here is the sample file updated as requested https://we.tl/t-lqNI1fBWg8

KPI Value = IF ( SELECTEDVALUE ( Table1[KPI] ) IN { 2, 3 }, FORMAT ( SELECTEDVALUE ( Table1[Value] ), "Percent" ), IF ( SELECTEDVALUE ( Table1[Year Month] ) IN { "202202", "202203" }, SELECTEDVALUE ( Table1[Value] ) & "%", SELECTEDVALUE ( Table1[Value] ) ) )