Difference between values from two different tables, on and a Lookup value from another table

MMoll — Mon, 23 May 2022 13:16:23 GMT

I have a list of whatsits (under NDA, so obfuscating source data), each with an assigned "level" and large number of parameters that have to meet certain minimum requirements for that level. The requirements are different for each parameter and vary with level.

So I have two tables. The first table lists the current level for each whatsit and the score for each parameter.:

tbl_Data

Title	Current Level	Parameter A	Parameter B	Parameter C	Parameter D	Parameter E
sample1	1	2	4	3	2	1
sample2	3	4	1	2	2	4
sample3	4	3	4	1	4	5
sample4	2	1	2	2	3	4
sample5	1	5	5	1	5	2
sample6	1	1	2	3	2	3

The second table lists the parameter requirements for each level:

tbl_Requirements

Level	Parameter A	Parameter B	Parameter C	Parameter D	Parameter E
1	2	3	1	3	4
2	3	3	1	3	4
3	4	3	2	4	4
4	5	4	3	5	5

I need to check each individual value to see if it meets the requirements for the current level. So, for example, in Excel the conditional formatting flag (simulated in red text above) would look like:

=IF((XLOOKUP(Tbl_Data[@[Current Level]],Tbl_Requirements[[Level]],Tbl_Requirements[[Parameter A]])-Tbl_Data[@[Parameter A]])>0,TRUE,FALSE)

or, in english:

If this cell's value is lower than the required value for this parameter at the current level, then TRUE else FALSE

There's more data manipulation that needs to happen afterwards, so I need to do this in M or (preferably) DAX.

I've tried a few different things but I'm relatively inexperienced and I keep running into trouble trying to define the relationship/lookupvalue between the two tables.

I'd be grateful for any help you can provide. Code is nice, but if you're willing to walk me through it so I don't have to ask again next time it'd be hugely appreciated.

Re: Difference between values from two different tables, on and a Lookup value from another table

v-easonf-msft — Thu, 26 May 2022 07:50:19 GMT

Hi, MMoll

Try calculated columns as below:

Requirement A = LOOKUPVALUE( Requirements[Parameter A],Requirements[Level],Data[Current Level])

Result_A = IF(Data[Parameter A]<Data[Requirement A],TRUE(),FALSE())

New fields used to conditional formatting:(Format style:Rules)

Conditionalformatting_A = IF(Data[Parameter A]<Data[Requirement A],1,0)

or (Format style: Field value)

Conditionalformatting_A = IF(Data[Parameter A]<Data[Requirement A],"#FF0000","#000000")

Result:

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: Difference between values from two different tables, on and a Lookup value from another table in DAX Commands and Tips

Difference between values from two different tables, on and a Lookup value from another table

Re: Difference between values from two different tables, on and a Lookup value from another table