Forum Discussion
Switch function not working
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.
anacpint , can you please please post the field name and the table name for the source of your matrix row headers for CPV and PQR?
In any case, you can see from your results that the matrix only has blank values to use for Fact_RFTC[rft_classification] in most cells. The only cells for which there is non-blank data is for cells
Feb-22, Fact_RFTC[rft_classification] = Severe, and
Jul-22, Fact_RFTC[rft_classification] = Moderate.
In all the other cells, the conditional formatting measure only has a blank value for Fact_RFTC[rft_classification] available, and so cannot work out the cell colour.
I think you need to create a dimension table for all the possible [rft_classification] values (call the table dim_RFT), and then use the dim_RFT[rft_classification] column in your matrix and measure instead of Fact_RFTC[rft_classification]. You will need a relationship in your data model between the dim_RFT and Fact_RFTC on the rft_classification column.
The field name is type_of_report and the table is DimTypeReport.
I changed the measure to check if it recognizes the zeros from the RFTC_NoBlank and it does.
Maybe this test helps to understand the behavior...
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.
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!
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.
anacpint , thank you for the table name and field name used in the matrix. I would suggest trying this:
ColorSwitchRFT = VAR RFT = SELECTEDVALUE(DimTypeReport[type_of_report]) RETURN SWITCH( TRUE(), RFT="Correct", "#11A82B", RFT="Low","#A9CC0D", RFT="Moderate","#F2CB49", RFT="New_Requirement", "#12ABDB", RFT="Severe","#FF6961" )With the measure that you suggested this was the result:
I tried instead of type of report, use the rft_classification and the result was the same as before:
anacpint , I expected my measure to colour the rows correctly.
Could you please place this test measure onto the matrix and post the results?
ColorSwitchRFT_Test = VAR vSelectedTypeOfReport = SELECTEDVALUE(DimTypeReport[type_of_report]) RETURN IF(ISBLANK(vSelectedTypeOfReport ), "It is blank", vSelectedTypeOfReport )This was the result.
The problem is not related with the type of report, and I cannot built the measure around that field because I'm using that formatting for other matrix that doesn't contain the type of report. For this, I can only consider the values from Fact_RFTC[Countof] and the Fact_RFTC[rft_classification] to define the colors, nothing more.
anacpint , I am confused as to what you want to achieve for two reasons:
1: I asked what table and field is used on the matrix to display the Row header values for Low, Severe, etc. You told me it was DimTypeReport[type_of_report], but that is incorrect.
2: In your original post, it appears that you are attempting to colour the entire row of the matrix depending on the whether the row header is Low, Moderate, or Severe. Is this the case? Or do you want to colour each cell in the matrix based on the value of an existing measure?
1. My bad, I made confusion about your question. The field is the rft_classification.
2. That's correct, I want all the row with the same color.Sorry.
anacpint , Could you please clarify the name of the table that the field rft_classification comes from? To be certain of this, please go to the matrix visual, and on the Values section, hover your mouse over the field that is being used to display the "Low", "Moderate", "Severe" row headers on the matrix, then use Snipping Tool to post what field source is.
For example, in my matrix when I hover over a Values field called [Financial Period] I get this:
The values come from the Fact_RFTC[rft_classification], I don't have this field in any other table, just on this fact.
anacpint , you're welcome. Please update us on whether it worked or not.