Forum Discussion

JacobMotu's avatar
JacobMotu
Frequent Visitor
3 years ago
Solved

Conditional formatting table based on a target set

Hi community,

This is my simplified data model:

 




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

Need Help
  • JacobMotu's avatar
    JacobMotu
    3 years ago

    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 🙂

11 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    3 years ago

    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

     

    • JacobMotu's avatar
      JacobMotu
      Frequent Visitor
      3 years ago

      Hi Theo,

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


      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?

      • TheoC's avatar
        TheoC
        Community Champion
        3 years ago

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