Forum Discussion
Solved
DAX FILTER with multiple criteria
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])
)
RankGroup=MAXX(FILTER(TableB,TableB[Category]=TableA[Category]&&TableB[Min]<=TableA[Sales]&&TableB[Max]>=TableA[Sales]),TableB[Groups])
2 Replies
- wdx223_Daniel
Community Champion
RankGroup=MAXX(FILTER(TableB,TableB[Category]=TableA[Category]&&TableB[Min]<=TableA[Sales]&&TableB[Max]>=TableA[Sales]),TableB[Groups])
Thanks! Worked perfectly
