Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Ignoring an active relationship

Hello,

 

I have problem with filtering data in measure. I need to ignore the active relationship with the fact table from the date dimension and define the date that has to be filtered in the measure, keeping all other relationships with the fact table active and working.

The measure - 
var days = max(days[days])

var amount = CALCULATE(SUM('Table'[amount]),
FILTER(ALLSELECTED(Table),'Table'[first_date]+days <= MAX('_T Date'[Date]) && 'Table'[first_date]+days >= MIN('_T Date'[Date]) )
 )
return amount
 
Now this measure returns the correct result the calculation is correct and all the dates and amounts are showed correctly when used in a Matrix with the date and days dimension.

Now if i change the matrix to filter the data by different dimension, lets say customer gender - it ignores the active relationship.

The problem is the ALLSELECTED - it works when applying filters outside of the visualization.
 
So basically i need to find how to change the measure so that it ignore only the active date relationship.

Any help would be nice.
Thanks
 
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ，

     

    >>So basically i need to find how to change the measure so that it ignore only the active date relationship.

     

    You can use REMOVEFILTERS function in your formula to clear filters from the specified tables or columns. You can refer to https://docs.microsoft.com/en-us/dax/removefilters-function-dax

     

    You can try something like:

     

    The measure =
VAR days =
    MAX ( days[days] )
VAR amount =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            'Table'[first_date] + days
                <= MAX ( '_T Date'[Date] )
                && 'Table'[first_date] + days
                    >= MIN ( '_T Date'[Date] )
        ),
        REMOVEFILTERS ()
    )
RETURN
    amount

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

3 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ，

     

    >>So basically i need to find how to change the measure so that it ignore only the active date relationship.

     

    You can use REMOVEFILTERS function in your formula to clear filters from the specified tables or columns. You can refer to https://docs.microsoft.com/en-us/dax/removefilters-function-dax

     

    You can try something like:

     

    The measure =
VAR days =
    MAX ( days[days] )
VAR amount =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER (
            ALLSELECTED ( Table ),
            'Table'[first_date] + days
                <= MAX ( '_T Date'[Date] )
                && 'Table'[first_date] + days
                    >= MIN ( '_T Date'[Date] )
        ),
        REMOVEFILTERS ()
    )
RETURN
    amount

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

    • vstoppPCM's avatar
      vstoppPCM
      Frequent Visitor
      3 years ago

      Thank you so much!  This helped me get the correct numbers for a visual that was limited by a join.

      Valerie

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Join the first Date of your table with a date table and use all selected on the date table. It should allow other filter

     

    The measure -
    var days = max(days[days])
    var amount = CALCULATE(SUM('Table'[amount]),
    FILTER(ALLSELECTED(Date),'Date'[first_date]+days <= MAX('_T Date'[Date]) && 'Date'[first_date]+days >= MIN('_T Date'[Date]) )
    )
    return amount

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions


    Appreciate your Kudos.