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

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

 

 

 

  • JacobMotu's avatar
    JacobMotu
    Frequent Visitor
    3 years ago

    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!

    • TheoC's avatar
      TheoC
      Community Champion
      3 years ago

      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

       

       

      • JacobMotu's avatar
        JacobMotu
        Frequent Visitor
        3 years ago

        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!