Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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. 
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Please clarify further

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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