Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JacobMotu
Frequent Visitor

Conditional formatting table based on a target set

Hi community,

This is my simplified data model:

 

2023-05-29 11_53_41-Untitled Diagram.drawio - draw.io.png


2023-05-29 12_17_45-databasecomputer - Remote Desktop Connection.png

Test pbix file:
download

Goal
: I'm trying to highlight values in a report table (quite similar to table Results) that are below Min limit (one color) or above Max limit targets (another color).

In the example there is only Mg column in Results and one pair of Min and Max columns in Targets. In real I have many columns like Mg in Results and so corresponding limits for each in Targets. The reason for Targets table to exist is that they change over time so a person adds new target set into a database that can be used for Samples later on. Therefore I can't set static values in conditional formatting for each column but must based it on the Targets table.

For each entry (ResultID) I need to check Mg value against corresponding MgMin and MgMax from Targets based on matching TargetID for individual Sample.

Failed attempts: I tried to create a new column in Results with using IF function to get -1 when it's below, 0 when within, and 1 when above limits. That can I could use in conditonal formatting for each column to set three custom gradient colors. It can be only SUM, COUNT etc. though, and I can't figure out how to filter the rows within a column based on matching TargetID.

I would appretiate very much if someone point me to the right direction either suggesting the DAX formula or even different type of solution.

I'm stuck on this for two days now, tried to search through this forum and others (Quora, Stackoverflow), even asking one of the AI language models to help, but still can't find a working solution.

Thank you for your responses!
Kind regards,
Jacob

1 ACCEPTED SOLUTION

I already added the link for a testing pbix file into my original post yesterday, but forgot to mention it in the previous post.

I use this formula that works for me now:

 

MgCheck = 
VAR MgMinCheckVar =
    SUMX(
        Targets,
        CALCULATE(
            SUM(Targets[MgMin]),
            Samples[TargetID] = EARLIER(Targets[TargetID])
        )
    )
VAR MgMaxCheckVar =
    SUMX(
        Targets,
        CALCULATE(
            SUM(Targets[MgMax]),
            Samples[TargetID] = EARLIER(Targets[TargetID])
        )
    )
RETURN
    SWITCH(
        TRUE(),
        Results[Mg] > MgMaxCheckVar, 1,
        Results[Mg] < MgMinCheckVar, -1,
        0
    )

 

 

It is probably not the simpliest either the most efficient formula, but does the job. Also most of  the previous solutions work.

The reason why it kept giving me all those errors was because there is only one relationship with the Target tables which is the one with Samples table, but the Cross filter direction was set to Single. Once I switched that to Both, it works. I noticed that only because everything was working in the test model where Both was selected by default. I hope it will not cause any issues in the future with other visuals. But because even in my real model there is only one relationship to Target table, there is no ambiguity.

Thanks in advance for your reaction Theo. I hope you won't prove me wrong on this 🙂

View solution in original post

11 REPLIES 11
TheoC
Super User
Super User

Hi @JacobMotu 

 

You can create a Calculated Column to achieve what you're after.

 

In_Range = 

VAR SelectedValue = Results[Mg]

RETURN

    IF (
        MINX ( 
            ALL ( Targets ) , Targest[MgMin] ) <= SelectedValue 
            && MAXX ( ALL ( Targets ) , Targets[MgMax] ) >= SelectedValue,
		Targets[TargetID] , 0
    )

 

You may need to adjust the inputs to get the desired output, however, the result should give you 1 where it's TRUE and 0 where it isn't.

 

Let me know how it goes.

 

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hi Theo,

Thanks for your response. I'm getting this error:

JacobMotu_0-1685330761807.png


I don't understand why because in Targets table I have only one row with TargetID of "1". In Samples, most of the values in TargetID are BLANK (null) though as I have just added Targets table into the data model. Not sure it that might have caused the issue?

Hi @JacobMotu 

 

Apologies, just ignore the earlier one.  I just rebuilt a similar version of your model excluding the relationships.

 

Try this calculated column:

 

In_Range = 

CALCULATE ( 
    VALUES ( Targets[TargetID] ) , 
    FILTER ( Targets , Targets[MgMin] <= EARLIER ( Results[Mg] ) && Targets[MgMax] >= EARLIER ( Results[Mg] ) ) )

 

 

TheoC_0-1685332770354.png

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

You are a legend, thank you very much. Just a minute before your reply I found similar solution. With CALCULATE, as I understand, it gives me only boolean 1 for yes (within a range) and 0 for no (not within a range). With mine I get -1,0,1 based on if it's below min, within, or above max:

 

Check = IF(
    SUMX(
        Targets,
        CALCULATE(
            SUM(Results[Mg]),
            Samples[TargetID] = EARLIER(Targets[TargetID])
        )
    ) < SUM(Targets[MgMin]), -1,
    IF(
        SUMX(
            Targets,
            CALCULATE(
                SUM(Results[Mg]),
                Samples[TargetID] = EARLIER(Targets[TargetID])
            )
        ) > SUM(Targets[MgMax]), 1,
        0
    )
)

 

 

It works with only one row in Targets now and with only one Sample entry (two Results rows) populated. I hope it will work with more data, need to test. I'm not sure about the last part:

> SUM(Targets[MgMax]) - that might make a mess if there are more rows in Targets


Could you please either fix my code or adjust yours so it gives back -1,0,1?

I value your help highly, thanks for you time Theo!

Hi @JacobMotu 

 

It might be worth trying something like the below in a Calculated Column if you want a nested if style statement.

 

 

SWITCH 
	( TRUE () ,
		Results[Mg] > Targets[MgMax] , 0 ,
		Results[Mg] > Targets[MgMin] , -1 ,
		1 )

 

 

Otherwise, you can always integrate an IF statement with my earlier solution as well.

 

Hope this helps mate.

 

Theo

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

So today I fed the tables with more test entries just to have more rows everywhere.
Targets: 4 rows
Samples: 3 rows
Results: 3x20 rows (20 per sample)

Unfortunatelly neither of the solutions above works 😓

InRange (msg 4) gives me "A table of multiple values was supplied where a single value was expected.".

Check (msg 5) results with wrong output and when I tested each part, neither of them works correctly anymore.

Switch (msg 6) gives me "A single value for column 'MgMax' in table 'Targets' 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."

I'm getting desparete. Could you still help me please? I read the documentation on learn.microsoft.com for the syntax and explanation of the functions and trying to solve this, but no positive results. At least I learnt to fill in more entries before testing anything 🙂

Many thanks!

@JacobMotu 

 

Can you please create a PBIX file that has dummy data and matches your exact Relationship model and please ensure the Data Formats / Types are identical to that in your real model.

 

If you can do that, I'll be able to assist further.

 

Thanks mate.


Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

I already added the link for a testing pbix file into my original post yesterday, but forgot to mention it in the previous post.

I use this formula that works for me now:

 

MgCheck = 
VAR MgMinCheckVar =
    SUMX(
        Targets,
        CALCULATE(
            SUM(Targets[MgMin]),
            Samples[TargetID] = EARLIER(Targets[TargetID])
        )
    )
VAR MgMaxCheckVar =
    SUMX(
        Targets,
        CALCULATE(
            SUM(Targets[MgMax]),
            Samples[TargetID] = EARLIER(Targets[TargetID])
        )
    )
RETURN
    SWITCH(
        TRUE(),
        Results[Mg] > MgMaxCheckVar, 1,
        Results[Mg] < MgMinCheckVar, -1,
        0
    )

 

 

It is probably not the simpliest either the most efficient formula, but does the job. Also most of  the previous solutions work.

The reason why it kept giving me all those errors was because there is only one relationship with the Target tables which is the one with Samples table, but the Cross filter direction was set to Single. Once I switched that to Both, it works. I noticed that only because everything was working in the test model where Both was selected by default. I hope it will not cause any issues in the future with other visuals. But because even in my real model there is only one relationship to Target table, there is no ambiguity.

Thanks in advance for your reaction Theo. I hope you won't prove me wrong on this 🙂

@JacobMotu if you get it working the way you want, run with it big fella!  The beautiful part of Power BI (which can also be its weakness), is that there are so many ways of achieving the same (or closely aligned) outcome. 

 

Well done on going and finding the output you were after!  It pleased me to see that you didn't hold back and just wait for answers. Instead, you went out looking for them until you got the right one.  An exceptional attitude to have, mate!

 

Well done and keep doing great things!

 

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Theo, thanks for the guidance and support. I am very aware people reply here in their free time without any obligations to anyone or anything. It's great knowing people from community help each other. Probably my first but not the last show up in here 🙂

Cheers mate.

@JacobMotu I am glad to hear that, mate.  Thank you for your kind comment and awareness for what the Community and its members are about.  It certainly is what got me to fall in love with Power BI.  Wishing you all the best mate and hope to see you on here now and again! 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.