cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ExcelUnplugged Frequent Visitor
Frequent Visitor

KPI Statuses in a Switch formula

Hi,

 

In PowerBIDesktop or Excel

 

how can you write a DAX SWITCH formula that would switch over Statuses of different KPI's. So sort of

 

=SWITCH( [SwitchMeasure], [StatusOoKPIOne, [StatusOfKPITwo],...)

 

where KPI's were imported from Excel...

 

So to see the icons for the selected measure...

 

3 REPLIES 3

Re: KPI Statuses in a Switch formula

ur requirement is quite no clear, can you be more specific?

ExcelUnplugged Frequent Visitor
Frequent Visitor

Re: KPI Statuses in a Switch formula

Ok, so in a Data Model in Excel you have a bunch of measures and a bunch of KPI-s defined for those measures.

P2.pngP1.png

And then, instead of vasting many columns of valuable workspace, you use a disconected slicer method, so that you can easily change the calculation (so still have them all available), you wish to see and only use one column of real estate

P3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now with calculations this works great... My question however is, if you can write a magic SWITCH formula (or use another function) to use only one column on the KPI statuses (the icons).. Something like

P4.png

 

 

 

 

 

 

 

but with some sort of magic dust Smiley Happy 

 

 

danno Member
Member

Re: KPI Statuses in a Switch formula

HI @ExcelUnplugged, You can indeed do this in DAX, it works in Power BI, where you defined the KPI in separate measures, and chain them together in a single switch statement, based on a table of measure names (this config is a bit manual but it works). 

The Selected Measure is a Measure over a list of measures : Selected Measure = SELECTEDVALUE('Measure Table'[Measure Column])

 
Individual KPIs look like this: 
Store Performance KPI =
IF('Availability Ratio Measures'[Store Performance] < 0.8, "0",
IF('Availability Ratio Measures'[Store Performance] < 0.9, "1",
"2")
)
 

Then the flexible KPI is: 

Flexi KPI = SWITCH([Selected Measure],
"Store Performance", [Store Performance KPI]
) - you can include more measures and only format selected columns using your KPIs. 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 240 members 2,612 guests
Please welcome our newest community members: