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
zkurdi
Regular Visitor

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
5 REPLIES 5
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

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:

 

filter.gif

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix

 

Many thanks for your reply. I've tried to replicate this but it did not seem to work. I've addded a link to the image showing what my current dashbaord looks like and the modifications i'm trying to impement

 

link: https://drive.google.com/file/d/1lyEFdlCbvWgo1IFsl_U8UjGkl0cbHSnB/view <it seems i'm unable to attach to this reply or display image from this link>

 

Ideally, it would be best if i can create a view (filtetered table) based on the user selected year and month and then display data from that table the report

 

Thanks

Z.K.

 

 

 

 

Hi @zkurdi,

 

The issue with your setup is that you are using the week number on your information that works at a differente level.

 

Can you give a sample of the information that you are using just to make a simulated file and get you a setup of the visual?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.