Check to see if value in home table is greater than Limit table

[JKraft100]

Hello,

I have Table 'Export'[iron] which contains a value and also has a 'Export'[UnitType] assigned to that same dataset.  I have a limit table 'UnitTypeLimits'[Unit Type] and a column that has the limit 'UnitTypeLimits'[Fe]. I am looking to say use the 'Export'[UnitType] value, lets say it is "Axle", go to the 'UnitTypeLimits'[Fe] column and look for the row that contains "Axle" and check 'Export'[iron] against the 'UnitTypeLimits'[Fe] value and see if it is greater.  If it is greater, return "Red"

Anyone want to give it a try, I can't get the two tables to link up right. 

IF(

    AND(SELECTEDVALUE('Export'[UnitType]) == SELECTEDVALUE(UnitTypeLimits[Unit Type]),

        SELECTEDVALUE('Export'[Iron]) > SELECTEDVALUE(UnitTypeLimits[Fe])),

)

[Anonymous · Accepted solution]

Hi  @JKraft100 ,

Here are the steps you can follow：

1. Create calculated column.

Fe_Flag =
var _feyel=
SUMX(
    FILTER(ALL('UnitTypeLimits'),'UnitTypeLimits'[Unit Type]=EARLIER('Export'[UnitType])),[Fe_Yel])
var _fered=
SUMX(
    FILTER(ALL('UnitTypeLimits'),'UnitTypeLimits'[Unit Type]=EARLIER('Export'[UnitType])),[Fe_Red])
return
SWITCH(
    TRUE(),
    [Iron]<_feyel,"NA",
    [Iron]>=_fered,"Red",
    [Iron]>=_feyel&&[Iron]<=_fered,"Yellow")

Cr_Flag =
var _cryel=
SUMX(
    FILTER(ALL('UnitTypeLimits'),'UnitTypeLimits'[Unit Type]=EARLIER('Export'[UnitType])),[Cr_Yel])
var _crred=
SUMX(
    FILTER(ALL('UnitTypeLimits'),'UnitTypeLimits'[Unit Type]=EARLIER('Export'[UnitType])),[Cr_Red])
return
SWITCH(
    TRUE(),
    [Chrome]<_cryel,"NA",
    [Chrome]>=_crred,"Red",
    [Chrome]>=_cryel&&[Iron]<=_crred,"Yellow")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[JKraft100]

Hello, upon further testing there is one issue. 

Code works unless there is a [UnitType] that does not exist, if that is the case the result should be blank, but it shows a record.  as seen in the screen shot, there is no unit type of what is listed there but it is showing a flag?  The last column in this table is being pulled from the related table which shows nothing for a unit type.  The export table shows a unit type so my expected response would be, if there is no correlating unit type in my limits table, there should be no flag red or yel.

[JKraft100]

Works great - thanks!

[JKraft100]

Hi Ibendlin,  

First Table is called "Export", It houses all raw data.  I want have a relationship between 'Export'[UnitType] and 'UnitTypeLimits'[Unit Type].

SampleID	UnitType	Iron	Chrome
1688812	Axle	14	10
1688813	Axle	31	19
1688814	Engine - Diesel	88	2
1688815	Engine - Gasoline	120	4

Second table is called "UnitTypeLimits"

Unit Type	Fe_Yel	Fe_Red	Cr_Yel	Cr_Red
Axle	15	25	5	15
Engine - Diesel	80	125	5	15
Engine - Gasoline	100	150	5	15
Differential	500	1000	25	50

I looking to product a column in 'Export' that is [Fe_Flag] and that houses a "1" or "2" number or "Yellow" or "Red" based on 'export' data.  IT would compare the value based on the unit type and see if the value is greater than the value listed in the 'UnitTypeLimit' table.

It would say loop through the [SampleId] take the [UnitType] and check if the [Fe_yel] or [Fe_Red] applies or NA

Expected result would look like this 'Export' table.

SampleId	UnitType	Iron	Fe_Flag	Chrome	Cr_Flag
1688812	Axle	14	NA	10	Yellow
1688813	Axle	31	Red	19	Red
1688814	Engine - Diesel	88	Yellow	2	NA
1688815	Engine - Gasoline	120	Yellow	4	NA

Thanks for looking deeper.

[lbendlin]

No need to link them, use TREATAS to transfer filters.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...