Forum Discussion
Order of FILTER function
Good day,
I'm looking for some DAX help.
Edit: added sample
My model looks like:
| Customer | *→1 | Bridge | 1←* | Actions |
| Product | *→1 |
So note the facts (actions) are connected to a bridge table instead of directly to dimensions. This bridge is a crossjoin of both customer & product. In order to find the right action for the customer/product combination, I'm using DAX.
But now I'm faced with a challenge I can't seem to figure out. Though it shouldn't be so hard.
With a measure I would like to search for a 'Action'[Price] for a product/customer combination. The criteria are:
- I want to return a blank if no relationship with 'Action'[ActionKey] could be established (red)
- If there is a relation (second filter), I want to filter out the lowest 'Bridge'[Max] for each [ActionKey] (orange)
So after aplying the filters, I should have a virtual 1←1 relationship.
| Bridge | Action | |||
| ActionKey | Max | 1←* | ActionKey | Price |
| 50153-16 | 16 | 50000-16 | 5 | |
| 50000-16 | 16 | 50153-6 | 10 | |
| 50153-6 | 6 | 50000-6 | 15 | |
| 50000-6 | 6 | 100028-16 | 20 | |
| 100028-16 | 16 | |||
| 100028-6 | 6 |
I tried several options but I can't figure it out. Basicly the syntax below should work in my opinion, but it seems like the filters are being applied symultaniously which gives incorrect results.
ActionPrice =
VAR _filter =
FILTER ( 'Bridge',
RELATED ( 'Action'[Price]) <> BLANK () &&
'Bridge'[Max] = MAX ('Brigde'[Max]))
RETURN
CALCULATE ( SUM ( 'Action'[Price] ), _filter)
The filters seem to work correct individually, but not the combination.
Please try:
Filter combination = VAR _filter1 = [Filter 1] var _filter2 = [Filter 2] return IF(ISBLANK(_filter2),_filter1,_filter2)
4 Replies
- PVO3Impactful Individual
So to be clear. The expected result:
Bridge (after filter) ActionKey Max Price measure 1←* ActionKey Price 50000-16 16 5 50000-16 5 50153-6 6 10 50153-6 10 100028-16 16 20 50000-6 15 100028-16 20 - PVO3Impactful Individual
Nobody? Hoping this was an easy fix because I overlooked something.
- V-lianl-msftCommunity Support
Please try:
Filter combination = VAR _filter1 = [Filter 1] var _filter2 = [Filter 2] return IF(ISBLANK(_filter2),_filter1,_filter2)- PVO3Impactful Individual
Great, thanks a lot! Just the addition of the IF(ISBLANK, will end my sleepless nights.
Such a simple solution which makes me feel stupid 😀