Search code examples
powerbidaxpowerbi-desktopconditional-formatting

How to conditionally format a column based on Min and Max targets from another table in PowerBI?

I'm trying to highlight values in the Results table that are below MgMin (one color) or above MgMax 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. Ideal result would be outputs of -1 (below MgMin), 0 (within MgMin and MgMax), and 1 (above MgMax).

Link for pbix test file.

enter image description here

enter image description here

enter image description here

enter image description here

If it was Excel, I would have simply used IF, something like this:

MgCheck = IF(SUM(Results[Mg]) < SUM(Targets[MgMin]),-1,IF(SUM(Results[Mg]) > SUM(Targets[MgMax]),2,0))

But DAX makes aggregations even when new calculated column is created. I can't figure out how to call a specific row values to compare, even more from different tables based on matching TargetID.

Another failed attempt:

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

and another:

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

and another:

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

I would appretiate any help on this as I'm stuck on that for a few days and it's very important to me to solve this. Many thanks!

Solution

  • I found why I kept getting all the errors. Some of the formulas worked in a test sample so I checked against my real model. I had only Single direction for Cross Filter direction on the relationship between Targets and Sample table. And because this is the only connection to Targets table in the real model as well, it did not work. So I just switched to Both (which was selected in my test pbix file by default when I source the tables from a spreadsheet).

    Because there are no other relationships for Targets table, I believe there is no ambiguity and it shouldn't create any unexpected behavior. More experienced people, please, confirm my assumption or tell me otherwise.

    This is the formula I currently use and seems to be working:

    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
    )

    If anyone know how to make this shorter / more efficient, I welcome your suggestions too.

    This is how it looks like. MgMinCheck and MgMaxCheck are introduced to show correct Min and Max values for corresponding SampleID / TargetID row values.

    enter image description here