Bar color change based on value to target

[nleuck_101]

Hello All,

I've done conditional formatting before with success but this time I'm running into issues. I have a total measure and and target measure. My relationship betweeen the two tables is many to many.
COUNT(total column)
SUM(target column) 

I created a conditional formatting measure as well.
IF([Total count measure] > [Target measure], "#EB5559", "#262A76")

As you can see it's not workin in my bar chart as a total, but if I add our sections then it works.
Total:

Section breakdown:

Any ideas on why it works on the section breakdown but not the total? Would it have anything to do with my many to many relationship on the sections?

[Anonymous]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @nleuck_101 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.You can try using a card visual to output the results of the two measures first. If the Total count measure is indeed less than the Target measure, then the colour of the total section should be correct. The reason the colour is correct after adding sections is that in some sections, the Total count measure is greater than the Target measure, as shown in my example data.

2.Secondly, your approach is correct. We recommend using one-to-one, one-to-many, or many-to-one relationships, as many-to-many relationships can indeed cause issues. Creating an intermediary table to handle many-to-many relationships can avoid the problems associated with direct many-to-many relationships.

Additionally, you can check the filter direction in the relationships. If it is a many-to-many relationship, it is recommended to use both.

If the above methods do not resolve your issue, please answer my questions, such as which fields are used to connect the two tables and which fields are referenced in your visual object. Of course, you can also modify my test data based on my PBIX attachment.

I hope these suggestions are helpful to you! If you have any other questions, feel free to let me know.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nleuck_101]

@Anonymous 
Your solution to adding an intermediary table I think worked 😅. For the previous year the total was over the target and bar changed red. The issue is that for the current year, I don't have the current target number quite yet meanting Target is Blank. If I add the following to my measure, my bar doesn't change color no matter what year is selected. Do you notice if I'm doing anything wrong?

Formatting bar color measure:
IF([Target measure] = BLANK(),
   "#262A76",
   IF([Count measure] > [Target measure],
       "#EB5559",

       "#B93397"

    )

)

[Anonymous]

Hi, @nleuck_101 
Thank you for your quick response.

1.First, regarding the measure, I recommend using the ISBLANK() function as a condition. This ensures that the logic is more rigorous and accurate when handling blank values.

For more details, please refer to the relevant documentation.

ISBLANK function (DAX) - DAX | Microsoft Learn

2.Secondly, please check if the date column used as a slicer comes from a dimension table. If the field used is not from a dimension table, it may cause incorrect filter direction and fail to propagate correctly. This is because dimension tables are typically used to store descriptive data and play a role in connecting fact tables in the data model, ensuring that the slicer can be correctly applied to the relevant data.

For more details, please refer to the relevant documentation.

Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table - RAD...

Tutorial: From dimensional model to stunning report in Power BI Desktop - Power BI | Microsoft Learn

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nleuck_101]

@Anonymous 

ISBLANK didn't work either. I'm wondering if it's because the intermediary table isn't connected to my date table. For the bar chart I'm suppose to use the Section from the intermediary table correct? Below is a look at my relationships if that helps.

[Anonymous]

Hi, @nleuck_101 

Indeed, as you suspected, using the Section from the intermediary table is the correct approach. In your screenshot, both the section table and the dimdate table are dimension tables. We recommend using fields from the dimension tables as slicers.

Additionally, as seen in your screenshot, the filter direction in the relationships indicates that filtering from the dimdate table to the section table is not feasible. Understanding the filter direction in table relationships can help you structure your data model more effectively.

For more details, please refer to the following documentation:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nleuck_101]

@Anonymous 

If I now use my Year slicer from my fact table my target doesn't change.

Current Year should be blank until I recieve the target value. There's not a relationship to my fact table since I created the intermediary table.

[Anonymous]

Hi, @nleuck_101 

Thank you for your prompt response.

Since your DAX involves two fact tables, using a field from one fact table as a slicer will cause the part of the DAX that needs the other fact table to be filtered to always return a fixed value. This is why we recommend using an intermediary table as the slicer, and you can clarify this based on the filter direction.

Based on the screenshot you previously provided, we suggest using fields from the dimdate table as the slicer.
 

Best Regards,

Leroy Lu

[nleuck_101]

@Anonymous 

That's what I was originally doing. My Year slicer was from the DimDate table but the color wasn't changing because you told me to use the Section from my intermediary table which is not connected to my DimDate table.

[Anonymous]

Hi, @nleuck_101 

Thank you for your response and for clarifying the misunderstanding between us:

1.First, please check that the Y-axis field in your visual object comes from the intermediary table section

2.Secondly, ensure that your time slicer comes from another dimension table.

3.Then, my result is as follows:

4.Lastly, I have created an example. Please see the attachment.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nleuck_101]

@Anonymous 

I don't want to show each section. I know that works. My issue is showing one bar for the total and it changing colors based on the target.

[Anonymous]

Hi, @nleuck_101 
Thank you for your patient response. Based on your current data structure, you can try creating a calculated column in the section table as the x-axis, for example:

Column = 1

Here is my final result:

You may want to note that the color of the X-axis remains the default color of the body if no fields are filled in.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Could be. Usually the target should be in a disconnected table.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[nleuck_101]

Please do not ask me to provide sample data. I don't have free time to create a sample report with made up data. I'm simply just asking if anyone has a solution or has ran into the same problem and solved it.