Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stfox
Helper I
Helper I

Creating a Universal filter that acts on two columns

Hi Folks

I want to create a  single "universal" Year-Month  filter that will act on two different Date columns ( "Enrolment Date", and "Exit date") and the measures that flow from each of them.

 

So for example if I select Year-Month = "August 2016" (the universal filter), I only want August 2016 enrolments to be shown on the enrolments  graphic, and only August 2016 "Exits" to be shown on the exits graphic. 

 

Current Setup : I have two tables

1) A customer table with CustomerID and Enrolment_date  and Exit date (where applicable)

2) A date Dimension table (where I want the universal filter to be sourced from). 

 

I am struggling to figure out a methodology to enable this "universal filter" to act on two seperate columns. Can someone suggest an approach?  My gut feel is this is going to Involve a :CALCULATE and or RELATED typle logic. 

 

Link to the sample Power file is here

 

Screenshot with explanation here : https://drive.google.com/file/d/0B9J0yv0y0P5XUWFMZVJvYkR5NGM/view?usp=sharing

 

Thanks in Advance 

Steve

 

 

 

 

1 ACCEPTED SOLUTION

Thanks Lydia, Ankitpatira,

I think as a work around I will restructure the dataset in PowerQuery, so that I have a single "Date" column, and then an additional column ("Type of Date") that classifys what sort of date it is (enrollment or exit etc) as shown in the screenshots below.  This then allows me to use the date column as a "universal filter (Gneric_Month_Year). 

 

Working Example pbix file is available here

 

Thanks again for your input on this.


Cheers

Steve

 

 

Screenshot_Single_date.PNG 

 

Example Graphic.PNG

View solution in original post

4 REPLIES 4
ankitpatira
Community Champion
Community Champion

@stfox Because in pbi you can't create relationship between one column of date dimension and two columns of fact, easiest way to achieve this is to go to query editor ->right click your customers table and duplicate it. Then in one customers table keep enrolment date and in other keep exit date -> Close&Apply. Then create relationship between date dimension and both the queries for dates. Now you will have single column that can filter both for the visuals.

Thanks Ankitpatira 

Yes that definitely works (PoC example attached for anyone who wants it).   Its a bit clunky for me, as in my situation there may actually be additional dates (beyond Enrollment & Exit dates) such as assesment dates, reassement dates that I need to create measures for. Therefore this approach with lead to a proliferation of tables that would get a bit messy. 

 

After posting the initial question, I was thinking that is is the type of scenario where user defined Parameters should in theory work.

 

So for example.....CALCULATE  the  distinct count of customerIDs where enrolment Dates = Month X,.  With Month X being a parameter.  However I have stuggled to figure out how to enable users to dynamically set a parameter within PowerBI.

 

Does anyone else have any suggestions on how to create a universal filter  (using parameters or any other approach).

 

Cheers

Steve

Hi @stfox,

Based on my test, we are not able to set a parameter in your scenario to filter the two visuals at the same time as the two date columns locate in a single table. If you want to utilize parameter, you would need to duplicate your table keep date columns in two different tables. For more details about how to work with query parameter in Power BI Desktop, you can review the following blogs.

POWER BI DESKTOP QUERY PARAMETERS, PART 1

Query Parameters in Power Query – Part 1

Moreover, I agree with ankitpatira. I can’t think of other methods except this method that create relationship between date table and both the queries for dates.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lydia, Ankitpatira,

I think as a work around I will restructure the dataset in PowerQuery, so that I have a single "Date" column, and then an additional column ("Type of Date") that classifys what sort of date it is (enrollment or exit etc) as shown in the screenshots below.  This then allows me to use the date column as a "universal filter (Gneric_Month_Year). 

 

Working Example pbix file is available here

 

Thanks again for your input on this.


Cheers

Steve

 

 

Screenshot_Single_date.PNG 

 

Example Graphic.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.