Forum Discussion

zkurdi's avatar
zkurdi
Regular Visitor
8 years ago

Filter Based on Multi User Selected Values

Hello

New to PowerBi here so i apoloigze if this has been posted before, but i've tried searching for few days now with no luck

 

So

I have PowerBi read a table from Jira. Key columns are Created Date and Resolved Date

What i need to give the user the option to select reporting year and reporting month and have that applied to all visuals in the report

Logic is: in month X issues will have:

Created Date <= SelectedReportingDate

Closed Date   >= SelectedReportingDate

 

This would usually be resolved easily in Pivot table in excel

 

After many attempts i came with the following

1- Created date table = CALENDAR(DATE(2016,1,1),TODAY())

2- Added 3 extra columns, Year, Month Name, Month Number

3- Added Year and Month Name on the report as slicers

>> All ok so far

 

4- Created 2 columns on source table to show created and closed dates as YYYYMM
Example: _CreatedMonth = FORMAT(GetIssues[created],"YYYYMM")

 

5- Created a measure the Joins Selected value of year with selected value of month number

_ReportingMonth= CONCATENATE(SELECTEDVALUE('Dates'[ Year]),SELECTEDVALUE('Dates'Month #]))

 

> this works fine and give me value of 201802 for user selected year 2018 month February

 

Now this will only work if a single value is selected, is there any way to make the value look for the MIN value selected?
>I.e. if multi select is enabled and users select 2 year values the measure breaks and gets null

 

Eventually, i tried to create a table that will filter based on this comparison

FilteredTable = FILTER(GetIssues,[_CreatedMonth]<=[_ReportingMonth] && [_ClosedMonth]>=[_ReportingMonth] )

 

The table is always returning 0 records

 

I'm sure there should be a much easier way to do this and would appreciate any advice

 

Many thanks

 

Example:

Based on the table below:

  • if the user selects reporting month as Jan: he will only see ticket 1
  • if the user selects Feb 2018: he will see ticket 1 and 2 
  • If the user selects Mar 2018: he will only see ticket 3

 

Ticket #CreatedClosedMonths that would be open
Ticket 11-Dec-171-Feb-17Dec, Jan, Feb
Ticket 21-Feb-1815-Feb-18Feb
Ticket 31-Mar-183-Mar-18Mar
Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi zkurdi,

     

    Taking into that you have two columns on your model that have a date format this can be solved in differents ways depending on the slicing you want to have but also on the visuals you want to use.

     

    But assuming that you are using the calendar table withou any relation to the Data table you need to calculate your measure with a filter something like this:

     

     

    Measure =
VAR First_date =
    MIN ( Calendar[Date] )
VAR Last_Date =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Column] );
        Table[OpenDate] >= First_Date
            && Table[OpenDate] <= Last_Date;
        Table[CloseDate] >= First_Date
            && Table[CloseDate] <= Last_Date
    )

    When you select a month or more on your slicers this will return the result based on open and close dates, but be aware that this formulas need to bne adjusted to your model and your needs.

     

    Some probably will not even need to have this filters if you do a not active relationship between the two columns and the calendare table and use the USERRELATIONSHIP formula to filter out the information.

     

    Without any specific information or examples is difficult to give you an exact answer but there are several option on PBI.

     

    Regards,

    MFelix

    • zkurdi's avatar
      zkurdi
      Regular Visitor
      8 years ago

      Hello MFelix

       

      Thanks for the update. I've added an example above. I'll need to give the users the abilty to drill down and see actual records, so sum and count. while they would work fine usually, user wont be able to see records.

       

      Can i also ask you please for the value (i.e. why use) VAR in a measure ?

       

      Many thanks in advance

       

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi zkurdi,

         

        Add a Calendar table to your model not related with any of your columns then add following measure to your model:

         

        Count_Project = 
VAR Last_Date =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        COUNT(Tickets[Ticket #] );
        Tickets[Closed] <= Last_Date
    )

        Add this measure as a filter to your table when is greater than 0 should filter out your information:

         

         

        Regarding you question on the Max part I use a variable in order to have values inputed on the measure, On the formula we are filtering out all the dates that are lower than the selected date you can't use a MAX function directly on the calculated filter part so using a variable you are able to make it pass the parameter you need.

         

         

        Regards,

        MFelix