How to: create logical filter for multiple columns

[Anonymous]

Hello,

I am looking for solutions to create a customized logical filter in PowerBi.

To be specific, I have columns of create_date and end_date which imply the active subscription time for that member.

The most ideal output is to create buttons called such as Q1 2021/ Q2 2021 etc. so that once I click on the filter, I am able to trace back the active subscription records in the past.

Thank you in advance!

 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

you can try this,

-

1. create a calendar table, put column YQ into slicer, 

2. then create the measure to filter the table,

Measure = 
    var _selectedDate=CALCULATE(MAX('calendar'[Date]),ALLSELECTED('calendar'[Date]))
return 
    IF(ISFILTERED('calendar'[YQ]),IF(MIN('Table'[create_date])<=_selectedDate&&MIN('Table'[end_date])>=_selectedDate,1,0),1)

show items when the value is 1,

result

if you want to filter "Q2 2021" for create_date <= "2021-06-30" OR end_date>= "2021-06-30" , just change "&&" to "||".

Best Regards,

Community Support Team _Tang

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

[Ashish_Mathur]

Hi,

Using the M code technique here, for each ID, create one row per month between (and including) the strt date and end date.  So via this technique, all dates will now appear in a single column.  Create a Calendar Table and build a relationship from the Date column of your base data table to the Date column of your Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month Name, Month number and Quarter.  Sort the Month name by the Month number.  To your visual, drag any date dimension from the Calendar Table.

Hope this helps. 

[Anonymous]

Hello Ashish,

I appreciate your response! However, I think your solution does not fit my scenario.

Specifically, I would like to have a button called "Q1 2021" which filters the date range as create_date <= "2021-03-31" and end_date>= "2021-03-31", "Q2 2021" for create_date <= "2021-06-30" and end_date>= "2021-06-30", etc.

Thank you!