Forum Discussion

PVO3's avatar
PVO3
Impactful Individual
5 years ago
Solved

Order of FILTER function

Good day,

 

I'm looking for some DAX help. 

Edit: added sample 

 

My model looks like:

Customer*→1Bridge1←*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 
ActionKeyMax1←*ActionKeyPrice
50153-1616 50000-165
50000-1616 50153-610
50153-66 50000-615
50000-66 100028-1620
100028-1616   
100028-66   

 

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

  • PVO3's avatar
    PVO3
    Impactful Individual

    So to be clear. The expected result:

    Bridge (after filter)     
    ActionKeyMaxPrice measure1←*ActionKeyPrice
    50000-16165 50000-165
    50153-6610 50153-610
    100028-161620 50000-615
        100028-1620
  • PVO3's avatar
    PVO3
    Impactful Individual

    Nobody? Hoping this was an easy fix because I overlooked something.

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support

      Please try:

      Filter combination = 
      VAR _filter1 = [Filter 1]
      var _filter2 = [Filter 2]
      return IF(ISBLANK(_filter2),_filter1,_filter2)

       

      • PVO3's avatar
        PVO3
        Impactful 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 😀