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 

 

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!

  • TheoC's avatar
    TheoC
    Community Champion
    3 years ago

    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

     

    • JacobMotu's avatar
      JacobMotu
      Frequent Visitor
      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

        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 🙂