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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
23jools
New Member

Format a dax query based on a slicer choice

Hi all

I'm quite new to PowerBi as well as this forum.  I wondered if someone could help me with a problem i'm having with my dax formatting.  I currently have a simple dax query calculating a divide of two fields in the same table.  My slicer is from my "IIForder" table which has 14 choices in field [IIF], 13 of these are % so i have easily formatted these using the format from the ribbon.  I now need to change just 1 of these options "ES-02" to average.   I've tried the iscrossfiltered solution but i don't think i've written this correctly.  

 

% IIF = DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))
 
Thanks in advance for any help regarding this query.
 
Julie
6 REPLIES 6
23jools
New Member

I've managed to resolve this.

 

Thanks again

 

Julie

Glad i could help,

 

if you have multiple scenarios you should look to use a Switch Function.

https://docs.microsoft.com/pt-pt/dax/switch-function-dax

 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

jcalheir
Solution Supplier
Solution Supplier

Hi,

 

You can use a IF function to test your slicer value, dont know you actual data, but you can try something like this

 

IF(
    SELECTEDVALUE('IIFolder'[choice]) = "ES-02",
    
    --Your average formula bellow
    AVERAGE((sum(DataPBI[Numerator])),
    DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))
    
   

)

 Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Thanks Jose

 

I'm getting an error message regarding the parameter not being the correct type.  Both my numerator and denominator are whole number formats

23jools_0-1659959053792.png

 

Thanks

 

Julie

It's normal, i misspeled the second argument. remove the SUM and just do the average from the Numerator.

 

IF(
    SELECTEDVALUE('IIFolder'[choice]) = "ES-02",
    
    --Your average formula bellow
    AVERAGE(DataPBI[Numerator]),
    DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))
    
   

)

 

I guess this should do

 

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Hi José

 

This has fixed my avg but now i need to get the other choices as a % and add the "Avg" after the average sum.  Sorry to be a pain, can this be done.  Below is my attempt to do this that has failed.

 

% IIF = IF(
SELECTEDVALUE('IIForder'[IIF]) = "ES-02",
AVERAGE(DataPBI[Numerator]),
divide(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))& " Avg"
if(
SELECTEDVALUE('IIForder'[IIF]) <> "ES-02",
divide(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))/100 & " %"))
 
Thanks again
 
Julie

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors