Two filters for one column, filter independence.

[ewelinag17]

Dear,

I have a proble.

I have a table.

I want to create a report that will count the number of loans from the selected period and divide by the number of loans from the second selected range. One line is one loan. I want to report two filters with date selection and displayed result. I don't know how to make these two filters independent from each other. How to get it? Need I to create a new table? Can I create a measure? If so, what will it look like?

Thank you in advance for your help.

best regards

[AntrikshSharma · Accepted solution]

@ewelinag17  You will have to create duplicated date tables that are disconnected from the model, then use columns from these table in the slicers and then use it for filtering the dates in the table that is connected to the rest of the Model. here is an example.

=
VAR DisMinDate =
    CALCULATE (
        MIN ( DisconnectedDates[Dates] ),
        ALLSELECTED ( DisconnectedDates[Dates] )
    )      -- Min Date from the slicer which is using column from disconnected dates table
VAR DisMaxDate =
    CALCULATE (
        MAX ( DisconnectedDates[Dates] ),
        ALLSELECTED ( DisconnectedDates[Dates] )
    )      -- Max Date from the slicer which is using column from disconnected dates table
VAR FilterDates =
    FILTER ( ALL ( Dates ), Dates[Date] >= DisMinDate && Dates[Date] <= DisMaxDate ) 
VAR Result =
    CALCULATE ( [Measure], FilterDates )
RETURN
    Result

[amitchandak · Accepted solution]

@ewelinag17 , refer my blog on using two date slicers and how to compare data across those

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[AntrikshSharma · Accepted solution]

@ewelinag17  You will have to create duplicated date tables that are disconnected from the model, then use columns from these table in the slicers and then use it for filtering the dates in the table that is connected to the rest of the Model. here is an example.

=
VAR DisMinDate =
    CALCULATE (
        MIN ( DisconnectedDates[Dates] ),
        ALLSELECTED ( DisconnectedDates[Dates] )
    )      -- Min Date from the slicer which is using column from disconnected dates table
VAR DisMaxDate =
    CALCULATE (
        MAX ( DisconnectedDates[Dates] ),
        ALLSELECTED ( DisconnectedDates[Dates] )
    )      -- Max Date from the slicer which is using column from disconnected dates table
VAR FilterDates =
    FILTER ( ALL ( Dates ), Dates[Date] >= DisMinDate && Dates[Date] <= DisMaxDate ) 
VAR Result =
    CALCULATE ( [Measure], FilterDates )
RETURN
    Result