Conditional formatting disappearing when new column added

[Anonymous]

Hi all,

I currently have my data conditionally formatted to turn red if the value is more than 5% as shown below and it works perfectly.

I used the following:

Background Colour = SWITCH(true(),'Cable Schedule'[% Volt Drop]>=0.05,"red")

My current table visualisation shows data from Cable Schedule and datasheet and Bridge Table.

However, when I add a new column from loadlist, all my conditional formatting no longer works. How can I fix this? 

[v-kelly-msft]

Hi  @Anonymous ,

When you drag the table visual down,you will see the formatting works:

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

Yeap the conditional formatting works when I remove my sensitive data I am not sure why.

Hoever, the data in the table keeps repeating itself and I am unable to fix that.

Thank you.

[v-kelly-msft]

Hi @Anonymous ，

If you add the sensitive data,will the data with contional formatting still exists in the table visual?I'm guessing it's not a contional formatting issue,but a table relationship issue...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

I would think so too. Maybe if the issue of the multiple rowa appearing is solved, the conditional formatting will appear fine too? The issue I'm having now is the multiple repeated rows.

Thanks.

[AllisonKennedy]

What happens to the actual value of the % Volt Drop number when you add load list column? Is it still calculating properly?

I'm not sure what your Bridge table is doing? You still have many-many relationships which are typically not good.
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html

[Anonymous]

@AllisonKennedy 

The number is fine and appears as it should however, the conditional formatting no longer works and I can't seem to figure out why.

I tried doing a Bridge Table in power query and it made the values of my load list column appear repeatedly for every single row. 

I  tried to solve it in this thread https://community.powerbi.com/t5/Desktop/See-all-rows-in-tabel-visualisation/td-p/1350674 and still cant seem to solve the many-to-many issue. 

[AllisonKennedy]

@Anonymous
Wow! quite an exchange in that thread- I suspect this conditional formatting is a similar issue to the other one you posted about. What is the DAX for the % Volt? If it thinks there may be multiple values or rows detected, this may possibly throw off the conditional formatting, though seems strange if the measure is still working.

Can you describe the purpose of each of your three tables (bridge I understand, but the other three please) and why they have duplicate values for each 'ID' in your bridge table?

[Anonymous]

@AllisonKennedy 

This is the DAX used:

% Volt Drop = ((RELATED(Datasheet[Volt Drop])*'Cable Schedule'[Load]*'Cable Schedule'[Length])/100)/900

The Cable Schedule is the main table. I am using the table visualisation to extract and get data from Datasheet through relationship [ID] to replace VLOOKUP which makes excel files big and slow. If the value of % Volt Drop>5%, it will turn red.

This is where I am at now.

 

 

I now want to extract the data from some columns in the loadlist when 'Cable Schedule'[Cable Number] = 'loadlist'[Tag]. There are rows in [Tag] which are not in [Cable Number] and vice versa. The elements in both are not unique and has some repeated values. A sample of the [Tag] is FA_Qa_13_01-09 (text element). I made the bridge to link those 2 up using 

Bridge Table =

DISTINCT(

UNION(

SELECTCOLUMNS('Cable Schedule',"Tag",[Cable Number]),

SELECTCOLUMNS('loadlist',"Tag",[Tag])

)

)

When I did the bridge in DAX, the relationship become 1 to many (both directions) however, I get an error message below when I try to add a column from loadlist.

   

 

Which made me use the many-to-many bridge table instead.

I just have no idea why the conditional formatting vanishes when I try to add in a column from loadlist.

Thanks in advanced for your help.

  

[AllisonKennedy]

@Anonymous
I have looked at your .pbix file
The conditional formatting you have applied is using a calculated column, so when you have the repeated rows problem, this conditional formatting doesn't know which value/row to look at and cannot return a valid result. Try using a measure instead for your conditional formatting.

As for fixing your repeated rows, why are there 2 different rates for the same Tag? Is there a date or sub category that helps you decide which rate to use? When you add rate to the table, what value do you want to see? Average? Max? A specific rate?

[Anonymous]

@AllisonKennedy 

What are the changes that I would have to make to change it into a measure as the DAX doesn't seem to work as a measure and get this error message when I try to change the Background Colour into a measure:

"A single value for column '% Volt Drop' in table 'Cable Schedule' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

% Volt Drop should just be calculated for every row of the data. 

Is there a better way I should approach this?

What do you mean by "why are there 2 different rates for the same Tag?"

I would like to extract the value of [rate] when [Tag] in 'Full List' corresponds to [Cable Number] in 'Cable Schedule'.

Thank you.

[AllisonKennedy]

@Anonymous

1) Your measure error stems from the fact that you have many rows for Volt drop for the same Cable number. In the pbix file you uploaded, your table visual is doing a SUM on these values, so you could try adding SUM() function to your DAX when turning it into a measure:
% Volt Drop Measure = SUMX('Cable Schedule',((RELATED(Datasheet[Volt Drop])*'Cable Schedule'[Load]*'Cable Schedule'[Length Metres])/100)/500)
2) You can use the built in conditional formatting on the matrix to apply it to the measure directly (no need for custom DAX for the formatting, it can be done using the Power BI tool).
3) In your sample data, these are the Tags/Cable Numbers that have more than one rate value:

As I mentioned in 1) above, your sample pbix is doing a SUM on the %VOLT drop, but what is the intended result when there is more than 1 value for Rate in the Full List table? 

[Anonymous]

@AllisonKennedy 

1) % Volt Drop Measure = SUMX('Cable Schedule',((RELATED(Datasheet[Volt Drop])*'Cable Schedule'[Load]*'Cable Schedule'[Length Metres])/100)/500). 

I tried it out and my values appear as 0.

I find it odd as for S/N 1-5, there is no cable number, hence I was expecting the cable rates to be blank and not have repeated rows.

3) Is there a way to show it the same number of times it appears? ie. A10A_3LLNPPA11A01 shows up twice once for 63 and once for 200? They are sub sections of the same cable number which I deleted the column as it contains sensitive data. 

Really appreciate you trying to help.

Thank you.

[v-kelly-msft]

Hi @Anonymous ，

Could you pls upload your .pbix file to onedrive business and share the link with us?(Remember to remove the confidential information).

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

Hi there, 

https://entuedu-my.sharepoint.com/:u:/g/personal/amya0001_e_ntu_edu_sg/EQQWgMXzwvpBh8uGZ7_noOAB-sUZi...

It can be found in the link below. I removed sensitive data and the conditional formatting works (my actual one still doesn't) but my data still appears repeatedly and I can't seem to solve that.

Thank you.

[v-kelly-msft]

Hi  @Anonymous ,

The link you provided is not public and I dont have access to it,pls make a public link and share it with me.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft 

Apologies about that.

Here it is: https://entuedu-my.sharepoint.com/:u:/g/personal/amya0001_e_ntu_edu_sg/EQQWgMXzwvpBh8uGZ7_noOABcjUGM...

Should work now.

Thank you.