Ignoring an active relationship

[Anonymous]

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

[v-deddai1-msft · Accepted solution]

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]

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

Valerie

[amitchandak]

@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.