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 🙂

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

     

     

     

    • 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