Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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
    6 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