cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amirabedhiafi
Skilled Sharer
Skilled Sharer

Format the measure depending on the filter selection

I have a filter Nombre/Montant in the static_table_filter[filter_name] :

 
 

I4eC2.png

 

If I select Nombre it will get the Montant measure, if I select Montant it will get the Montant measure

SwitchFilter = 
var selected = SELECTEDVALUE(static_table_filter[filter_name])

var Nombre = 'Measure'[Nombres]
var Montant = 'Measure'[Montant]

var result = 
switch(true(),
selected= "Nombre", Nombre,
selected= "Montant",Montant
)
return
result

I am putting the SwitchFilter in a stacked bar chart like below adding a column category :

 

lwy5S.png

I want when I select Montant the formatting in the chart will be in euros (adding a symbol €) and when I select Nombre the formatting will be in K like for example 5000 becomes 5K. How can I reach that ?

I am using a live connection to an SSAS tabular cube.

Amira Bedhiafi
Full Stack Business Intelligence Consultant @Capgemini
1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

Hi @amirabedhiafi 

It is possible to filter measures by slicer .But when display the value in Stacked bar chart , there is no way to dynamically change the text part of the X axis . I provided the steps , you can refer to them.

(1)Create a slicer table with measure name and corresponding value .For example :

Ailsamsft_0-1642745394859.png

(2) Then create two measures to associate the measure name with the value of the data table .

Montant = if(SELECTEDVALUE(Slicer[Slicer value])=1 || ISBLANK(SELECTEDVALUE(Slicer[Slicer value])),SELECTEDVALUE('Data'[Value]))
Nombre =
var _format=SELECTEDVALUE('Data'[Value])/1000
return 
IF(SELECTEDVALUE(Slicer[Slicer value])=2 ||ISBLANK(SELECTEDVALUE(Slicer[Slicer value])),_format)

(3)Set data format for these two measures in Model view .

Ailsamsft_1-1642745394861.pngAilsamsft_2-1642745394863.png

If you don’t know how to set , you can refer to the link below .

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

(4)Add a slicer with ‘Slicer’[Select Measure] and stacked bar chart with two measures .

The final result is as shown :

Ailsamsft_3-1642745394866.png

Ailsamsft_4-1642745394868.png

I have attached my pbix file , you can refer to it .

 

Best Regard

Community Support Team _ Ailsa Tao

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

View solution in original post

2 REPLIES 2
Ailsa-msft
Community Support
Community Support

Hi @amirabedhiafi 

It is possible to filter measures by slicer .But when display the value in Stacked bar chart , there is no way to dynamically change the text part of the X axis . I provided the steps , you can refer to them.

(1)Create a slicer table with measure name and corresponding value .For example :

Ailsamsft_0-1642745394859.png

(2) Then create two measures to associate the measure name with the value of the data table .

Montant = if(SELECTEDVALUE(Slicer[Slicer value])=1 || ISBLANK(SELECTEDVALUE(Slicer[Slicer value])),SELECTEDVALUE('Data'[Value]))
Nombre =
var _format=SELECTEDVALUE('Data'[Value])/1000
return 
IF(SELECTEDVALUE(Slicer[Slicer value])=2 ||ISBLANK(SELECTEDVALUE(Slicer[Slicer value])),_format)

(3)Set data format for these two measures in Model view .

Ailsamsft_1-1642745394861.pngAilsamsft_2-1642745394863.png

If you don’t know how to set , you can refer to the link below .

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

(4)Add a slicer with ‘Slicer’[Select Measure] and stacked bar chart with two measures .

The final result is as shown :

Ailsamsft_3-1642745394866.png

Ailsamsft_4-1642745394868.png

I have attached my pbix file , you can refer to it .

 

Best Regard

Community Support Team _ Ailsa Tao

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

MFelix
Super User
Super User

Hi @amirabedhiafi ,

 

This can be achieve using the calculated groups check the link below.

 

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!