Forum Discussion

stfox's avatar
stfox
Helper I
9 years ago
Solved

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.   ...
Need Help
  • stfox's avatar
    stfox
    9 years ago

    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

     

     

     

     

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

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.

stfox's avatar
stfox
Helper I
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • stfox's avatar
      stfox
      Helper I
      9 years ago

      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