IF Statement between two tables greater than OR less than

[blaferriere]

So I have two tables that have a relationship with each other. I am trying to narrow down my results based on a given price range from low to high.

Table 1 - Opportunities

Compatibility = A

Low Est. Value = 97,500

High Est. Value = 162,500

Table 2 - Items

Compatibility = A

Asking Price = 109,000

My validation would be either 1(true) or 0(false).

I basically want to remove all the other values that don't fall in range with the other tables asking prices. 

Any help would be greatly appreciated!

Thank you

[v-frfei-msft · Accepted solution]

Hi @blaferriere ,

To create a calculated column as below.

Column = 
IF (
    'Table 2 - Items'[Asking Price]
        <= MAX ( 'Table 1 - Opportunities'[High Est. Value] )
        && 'Table 2 - Items'[Asking Price]
            >= MAX ( 'Table 1 - Opportunities'[Low Est. Value] ),
    1,
    0
)

[Iamnvt]

hi,

you can try this calculated column:

Calculated Column = IF(RELATED('Item'[Asking Price]) < Opportunity[Highest] && RELATED('Item'[Asking Price]) > Opportunity[Lowest], 1,0)

or this measure:

Measure = 
VAR
selecteditem = SELECTEDVALUE('Item'[Compability])
VAR
high = MAX(Opportunity[Highest])
VAR
low = MIN(Opportunity[Lowest])
RETURN
IF(SUM('Item'[Asking Price]) > low && SUM('Item'[Asking Price]) < high, 1,0)

here is the PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk6pzIHivHVrfmXigAw