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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi 

 

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? 

yalesg_0-1648988389201.png

 

8 REPLIES 8
tamerj1
Super User
Super User

@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] )
    )
)

1.png

v-rongtiep-msft
Community Support
Community Support

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.

tamerj1
Super User
Super User

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
1.png
Rename the columns inside the code as convenient
2.png
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
3.png
Here is a sample file for your reference https://www.dropbox.com/t/EcbFqwYlnAqOF54F

 

Anonymous
Not applicable

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. 

@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 )
Anonymous
Not applicable

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

Please clarify further

Anonymous
Not applicable

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

yalesg_1-1651814768184.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors