cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
New Member

I've managed to resolve this.

Thanks again

Julie

Solution Supplier

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é

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",

AVERAGE((sum(DataPBI[Numerator])),
DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))

)

``````

Kind regards,
José

New Member

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

Thanks

Julie

Solution Supplier

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",

AVERAGE(DataPBI[Numerator]),
DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))

)
``````

I guess this should do

Kind regards,
José

New Member

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors