Switch function not working

[anacpint]

Hello,

First, I developed a measure to replace the nulls with zeros.

RFTC_NoBlank = IF(ISBLANK(SUM(Fact_RFTC[Countof])),0,SUM(Fact_RFTC[Countof]))

This the result of the measure.

After this step I created a measure to do the conditional formatting.

ColorSwitchRFT = VAR RFT = VALUES(Fact_RFTC[rft_classification])

RETURN

SWITCH(

TRUE(),

RFT="Correct", "#11A82B",

RFT="Low","#A9CC0D",

RFT="Moderate","#F2CB49",

RFT="New_Requirement", "#12ABDB",

RFT="Severe","#FF6961"

)

And this is the result:

So, this measure doesn't assume the zeros created by the first measure and don't change the color.

The final result should be something like this:

I'm working with live connection so I cannot create a calculated column.

Is there any way to solve this?

Thanks in advance.

Ana Pinto

[Anonymous]

2 Reasons Why The Two Way Switch Stopped Working

You’ve already got a brief idea about the reasons for this issue. But you need to go through them in detail to fix the issue. 

That’s why the reasons are given below along with the solutions-

Reason 1: Loose Connections

One of the quite common reasons for this issue is loose connections. Typically, connections that are loose cause the wire to cease to function. 

For two-way light switches to turn on, they must be turned in a specific direction. The direction is not too complex but simply explained in the packets of the switch. Because the connection is intermittent when the switch is left in this orientation. 

Solution

Check the two-way switch boxes to start troubleshooting. After that, look for any splices that are loose. Or search for a loose switch terminal. 

You must also see if the common wire is hot or neutral . Because you need to install the wire properly. 

Since hot and neutral wires have different functions, these wires have to be distinguished. Furthermore, any wire in the box, regardless of color, could be the source of the problem.

The wires connecting to the switches through the little push-in holes may be visible. You can refit the connections using the screws on the sides of the switches. 

But remember one thing! That is, while working with the wiring connections, wear safety goggles. These would prevent your eyes from directly coming in contact with the electric sparks.

Not sure what would be the best goggles to use for it?

Well, no worries! Because we’ve got a few suggestions here!

These are the products you can easily trust. Because these products never disappoint.

Remember another thing! That is, make sure to take help from the experts for this amendment. Because electricity can be extremely hazardous. 

A loose connection is a common cause of electric shock at home. If you didn’t know, electric shock can cause damage to your   body .

Reason 2: Wiring The Switch Incorrectly

Wiring the switch wrongly is quite a common issue while the installation. To begin with this issue, the switch connected to the neutral wire is common. 

Because the switch will not have a power supply in that situation. As a result, it would be unable to take the order to turn on or off.

Plus wiring the switch in the wrong way can create additional hazards. That is, touching the switch might shock you for the wrong wiring. 

Not only that but it would also create short circuits. This can eventually create more difficulties. 

Plus this can also disrupt the shower isolator switch . Because connecting the wire of any switch incorrectly can result in difficulties with other switches.

Solution

This problem is a serious issue and needs to be fixed as soon as it’s recognized. To fix this, you must contact expert electricians. 

Make sure you don’t take any steps regarding this mechanism yourself. Because dealing with the electrical wires is quite dangerous as you already know.

These are the reasons and solutions for this issue of a two way switch.

Regards,

Rachel Gomez

[Anonymous]

@Anonymous , very funny 😂

[anacpint]

@Anonymous Hello, I think you're mistaken in your answer to me. I never talked about connection problem, Switch is a function, it is described on the the post...

[PaulDBrown]

Can you try this measure for the 0s?

RFTC_NoBlank = COALESCE(SUM(Fact_RFTC[Countof]),0)

[anacpint]

[Anonymous]

@anacpint , COLAESCE is only available in certain versions of Power BI. Instead, try

RFTC_NoBlank = IF(ISBLANK(SUM(Fact_RFTC[Countof])),0,SUM(Fact_RFTC[Countof]))

[anacpint]

That measure is the one that I developed first.

[Anonymous]

@anacpint , could you plase try this test measure and place it on the matrix? This will help work out what is going on.

ColorSwitchRFT_Test =
   VAR vSelectedClassification = SELECTEDVALUE(Fact_RFTC[rft_classification])
   RETURN IF(ISBLANK(vSelectedClassification), "It is blank", vSelectedClassification)

 Please post the results. 

[anacpint]

[Anonymous]

@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.

[anacpint]

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...

[Anonymous]

@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"
)

[anacpint]

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:

[Anonymous]

@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 )

[anacpint]

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. 

[Anonymous]

@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?

[anacpint]

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. 

[Anonymous]

@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:

[anacpint]

The values come from the Fact_RFTC[rft_classification], I don't have this field in any other table, just on this fact. 

[Anonymous · Accepted solution]

@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.