Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
Solved! Go to Solution.
@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.
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
You're welcome @Anonymous Please feel free to tag me in future and I hope it goes well sorting this one out.
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 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.
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
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
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.
Thank you again, I feel I'm so close with all your help, but I'm missing something fundamental.
Lee
@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?
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
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 1
Table structure
@Anonymous Can you clarify what the expected result should be compared to what you're currently getting please?
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
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
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"
)
)))
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"
)
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
HI there
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |