DAX FILTER with multiple criteria

[ybicalways79]

Hello,

This has been challenging to figure out.  I have tableA with ITEMs / Category / Sales.  I want add a column "Groups" to that table based on another table below.  lookup to category and then where the sales number fits between MIN and MAX columns.  Is that possible?

tableA

ITEMs

Item	Category	Sales
OTH-10	AC_CAPS	0
OTH-12	AC_CAPS	10
F-30	AC_FTG	0
H-22	AUXCOOL	0
H-23	AUXCOOL	1
H-24	AUXCOOL	26
H-25	AUXCOOL	101

tableB

Groups	Category	Min	Max
Unranked	AC_CAPS	0	0
A - Rank 1-25	AC_CAPS	1	25
Unranked	AC_FTG	0	0
Unranked	AUXCOOL	0	0
A - Rank 1-25	AUXCOOL	1	25
B - Rank 26-100	AUXCOOL	26	100
C - Rank 101-250	AUXCOOL	101	250

RankGroup =

CALCULATE( SELECTEDVALUE( tableB[Groups], BLANK() ),

    FILTER( ALL( 'tableB' ),

        tableA[Category] = tableB[Category] &

        tableA[Sales] >= tableB[Min] &

        tableA[Sales] < tableB[Max])

)

[wdx223_Daniel · Accepted solution]

RankGroup=MAXX(FILTER(TableB,TableB[Category]=TableA[Category]&&TableB[Min]<=TableA[Sales]&&TableB[Max]>=TableA[Sales]),TableB[Groups])

[ybicalways79]

Thanks!  Worked perfectly