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

tigzik

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!

tigzik

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

SQL get value based on corresponding min/max(value) from column in another related table

Get MAX value from one column and MIN from another column

Delete Rows not between Min and Max dates from another column

conditionally sum from one table based on information from another table

Extract all the data from a table based on max value of a column and grouped on another column

Conditionally Calculate Max and Min Values of a Column

Select MIN, MAX Corresponding column based on another column values

Creating a new Min() and Max() column based another dataframe

Min-max normalization in R, setting groups of min and max based on another column

How do I get the min/max of columns in table A and values from B where the rows of B are determined by another column in A?

How to create a column based on the min and max of another dataframe

Excel formula - How to conditionally fill a column with values from another table

Conditionally choose data from different columns based of data in another column

How to conditionally aggregate a column based on another

Excel: conditionally add icon based on min/max column

Keep min or max based on character in another column

How to add a new column with custom values, based on a WHERE clause from another table in PowerBi?

How to select the min and max date from a table into another table on the same row? in SQL

How to calculate yearly/monthly average, max value, min value etc of a Column based on the date (month/day/year) on another column in R

T-SQL Pivot table not using IN and values from another column in the pivot data based on MIN() & MAX

How arrange dates from min to max in columns based on unique id column value in excel

Select with min max from another table

How to fetch status column based on max/min of a timestamp column

How to create a table from another table in PowerBI and Sum a column

How to get min date and max date from pandas df based on another column

Conditionally update column from another column in same table

How to find value in a column based on min value from another column per group pandas

How can I aggregate a column's value based on the min() and max() values of another column?

Select the min or max date based on another column in sql