Forum Discussion
Switch function not working
- Anonymous4 years ago
anacpint , this is my suggestion:
Create a new Calculated Table called dimClassification:
dimClassification = VALUES('factRFTC'[Classification])Add a relationship between the dimClassification table and factRFTC table. Make sure the relationship is Many to One, and the direction flows from the dimClassification table to the FactRFTC table.
The measure that you use for the conditional formatting in the matrix should be changed to this:
ColorSwitchRFT = VAR vClassification = SELECTEDVALUE('dimClassification'[Classification]) RETURN SWITCH( TRUE(), vClassification = "Correct", "#11A82B", vClassification = "Low","#A9CC0D", vClassification = "Moderate","#F2CB49", vClassification = "New_Requirement", "#12ABDB", vClassification = "Severe","#FF6961" )Then delete the Fact_RFTC[rft_classification] field from the Rows field from the matrix, and add dimClassification[Classification] in its place.
Set the Conditional Formatting of the RFTC_NoBlank field in the matrix for Background Color to be Format Style "Field Value", and set the "What field should we base this on" to your [RFTC_NoBlank] measure.
When I do that with some test data, this is what I get. Note that my test data in FactRFTC has no rows at all for month March 2021, but the cell for {Mar-21, Low} is correctly coloured green because it gets the value for Classification from dimClassification.
Hope this helps.
The values come from the Fact_RFTC[rft_classification], I don't have this field in any other table, just on this fact.
anacpint , this is my suggestion:
Create a new Calculated Table called dimClassification:
dimClassification = VALUES('factRFTC'[Classification])
Add a relationship between the dimClassification table and factRFTC table. Make sure the relationship is Many to One, and the direction flows from the dimClassification table to the FactRFTC table.
The measure that you use for the conditional formatting in the matrix should be changed to this:
ColorSwitchRFT =
VAR vClassification = SELECTEDVALUE('dimClassification'[Classification])
RETURN
SWITCH(
TRUE(),
vClassification = "Correct", "#11A82B",
vClassification = "Low","#A9CC0D",
vClassification = "Moderate","#F2CB49",
vClassification = "New_Requirement", "#12ABDB",
vClassification = "Severe","#FF6961"
)
Then delete the Fact_RFTC[rft_classification] field from the Rows field from the matrix, and add dimClassification[Classification] in its place.
Set the Conditional Formatting of the RFTC_NoBlank field in the matrix for Background Color to be Format Style "Field Value", and set the "What field should we base this on" to your [RFTC_NoBlank] measure.
When I do that with some test data, this is what I get. Note that my test data in FactRFTC has no rows at all for month March 2021, but the cell for {Mar-21, Low} is correctly coloured green because it gets the value for Classification from dimClassification.
Hope this helps.
- anacpint4 years agoHelper I
I cannot create a calculated table or column because I'm working with Live connection with a SSAS database.
What I'm gonna do is to create that dimension directly on the cube and follow all your steps.
Thanks for all your help!
- anacpint4 years agoHelper I
Hello Anonymous,
I followed your suggestion and is working! Combining the classification from the Dim and the measure to replace the blanks, worked fine.
Thank you for your help.
- Anonymous4 years agoNot applicable
anacpint , you're welcome. Please update us on whether it worked or not.