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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Switch true losing page and slicer filters

I have been trying to add an if statement to no avail

 

My initial attempts have returned the value, however they have lost all my page filters and slicer filters and all the suppliers and contracts are being returned.

 

In short, i want to apply a star rating value 1,2 or 3 based on the score for the contract type. i have filter the page to only show the group of programmes and the remainder of the visuals and scores within the same visual are only populated for those filtered. However the 'Switch (True()' measure if bring all the schemes within the programme and assigning a score against all suppliers in my supplier table.

 

I have tried the following:

 

Star rating (Switch) = 

    SWITCH(TRUE(),
        [Imperative (average)]<0.1,"0",
        [Imperative (average)]<6,"1",
        [Imperative (average)]<8,"2",
        "3"
        )

 

This returns the results i want, however all contracts active from my page level filter are listed for all the suppliers in my master supplier table, with those not relavent as blank.

 

using the 'out of the box' quick measure, does appear to allow me to assign the values for 1,2 or 3 stars also.

 

Any help would be greatly appreciatted.

 

screen 3.jpg

 

screen 1.jpg

 

screen 2.jpg

 

Additional items i have tried

 

Star rating = 

VAR Selection =
SELECTEDVALUE ( Supplier_Name_Bridge[Level 3])
RETURN
SWITCH(TRUE(),
[Imperative (average)]<0.1,"0",
[Imperative (average)]<6,"1",
[Imperative (average)]<8,"2",
"3"
)

 

 

Star rating = 

KEEPFILTERS(SWITCH(TRUE(),
[Imperative (average)]<0.1,"0",
[Imperative (average)]<6,"1",
[Imperative (average)]<8,"2",
"3"
))

 

Full page view, showing page level filters, with 2 of the suppliers not generating a score.

 

Oneill1_0-1607195642066.png

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Anonymous Thanks for the explanations - I understand now what the problem is. Your Star measure is using the REPT() function, which will convert missing values to blank. https://docs.microsoft.com/en-us/dax/rept-function-dax

 

You can either try to filter the Star measure, or use built in Icon conditional formatting in your table/matrix (using the working 0, 1, 2, 3 measure): 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2019-feature-summary/#iconStyles

 

A final option could be to create the Star measure using SWITCH, the same way you have done the numeric measure, and simply repeat the UNICODE() as many times as you want for each condition. Does that make sense? 

Because you have some many to many relationships, this problem is further complicated, so I would need to see some sample data or spend some time with you to understand why those many to many relationships exist.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

11 REPLIES 11
AllisonKennedy
Super User
Super User

You're welcome @Anonymous  Please feel free to tag me in future and I hope it goes well sorting this one out.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

@Anonymous Thanks for the explanations - I understand now what the problem is. Your Star measure is using the REPT() function, which will convert missing values to blank. https://docs.microsoft.com/en-us/dax/rept-function-dax

 

You can either try to filter the Star measure, or use built in Icon conditional formatting in your table/matrix (using the working 0, 1, 2, 3 measure): 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2019-feature-summary/#iconStyles

 

A final option could be to create the Star measure using SWITCH, the same way you have done the numeric measure, and simply repeat the UNICODE() as many times as you want for each condition. Does that make sense? 

Because you have some many to many relationships, this problem is further complicated, so I would need to see some sample data or spend some time with you to understand why those many to many relationships exist.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi @AllisonKennedy 

 

Thank you, the information was really useful. i will take some time to go through and test in greater detail, however reading so far i can see my error.

 

I will accept as the solution and if i get lost i may tag you again if thats ok as this has been really useful.

 

Thank you so much

Lee

Anonymous
Not applicable

Hi @AllisonKennedy I will try and get on the laptop before taking the little one to gymnastics in the morning and replicate the data set without the supplier names.

 

In short I have tables for

 

The supplier scores, which is linked to

Contracts (work tasks)

Metrics (grouped by category to produce imperative measure)

Supplier names

 

 

When I produce the scores by the imperative measure, it will only bring back the score for the supplier based on the contract type I filter. Only displaying the contract they are active on.

 

When I introduce the first measure above, it will replicate the above scores as values 0,1, 2 or 3. 

 

When I introduce the star rating, it will display all contracts, even those the supplier isn't active on. Those they are not active on are returned blank. Further more, it displays all the suppliers in the supplier table, those without active contracts or with the contract type filtered. So introducing the star metric displays all contracts under all suppliers. 

 

It feels like I need to introduce a filter into the star measure, above as measure 2.

 

Measure 2.jpg

Thank you again, I feel I'm so close with all your help, but I'm missing something fundamental.

 

Lee

AllisonKennedy
Super User
Super User

@Anonymous 

In your original post, can you further explain those screenshots with the data blocked out. Sorry for the silly questions, I know it makes sense to you, but without knowing your data, and also having some of it deleted, I'm not clear what the problem is. The stuff you have a box around and point to, is that correct or not? Why not if incorrect? Should the SWITCH have a different value? Do you not want that row displayed? 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi @AllisonKennedy 

 

I really appreciatte the help.

 

When i use the measures provided above

the initial measure to display the values 0 - 3 works and adheres to the page level filters, which are to view the supplier score for only 1 contract type.

 

When adding the second part of the measure to display the stars. it ignores the page level filter and displays all the contracts against the differing contract types for every supplier (even if they are not within that contract).

 

Measure 2

Measure 2.jpg

Measure 1

Measure 1.jpg

 

Table structure

Tables 1.jpg

 

AllisonKennedy
Super User
Super User

@Anonymous  Can you clarify what the expected result should be compared to what you're currently getting please?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

hi @AllisonKennedy 

 

Thank you once again.

I am trying to assign a value (0,1,2, or 3) based on the measure calculation. The measure calculated score is an average of multiple metrics returned in the measure [Imperative (average)] and will be a range 0-10.

 

I tried if and this also didnt work and forums suggested Swithc was a better option, hopefully this will demonstrate what i am hoping to acheive:

 

If [Imperative (average)] is 0 = 0

If [Imperative (average)] is <6 = 1

If [Imperative (average)] is =>6 <8 = 2

If [Imperative (average)] is >8 = 3

 

Thanks again

 

Lee

Anonymous
Not applicable

Hi @AllisonKennedy 

 

I should have stated, when i perform the score for the measure. it only shows the score for those filtered to that group who have a score.

 

This is the If statement i attempted

 

(IF) Star Rating = 
IF( [Imperative (average)] = 0, "0",
   
    IF(
        [Imperative (average)] < 6, "1",
            IF(
        [Imperative (average)] < 8, "2",     
                    IF(
        [Imperative (average)] <10.1,"3"
    )
)))

 

 

 

AllisonKennedy
Super User
Super User

You may need to check that average is greater than zero too?

 

Star rating (Switch) = 

    SWITCH(TRUE(),
        [Imperative (average)]>0 && [Imperative (average)]<0.1,"0",
        [Imperative (average)]<6,"1",
        [Imperative (average)]<8,"2",
        "3"
        )

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

HI there

 

Hi @AllisonKennedy 

 

Thanks for the support.

 

Unfortunaley this didnt work, i had hoped for it to be a simple thing i had missed, this is really frsutrating me.

 

i have also tried to work through all possible relationships in the below table affecting the visual.

 

TABLES.jpg

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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