Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
@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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |