Forum Discussion

domi25's avatar
domi25
Frequent Visitor
1 year ago
Solved

Filtering the data

I have a 'Projects' table in my report with a 'project start date' column. I have two tables 'Period' and 'Month+year' they are connected to the 'project start date'. In the report, I would like to h...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for dharmendars007 's reply ,please allow me to provide another insight.
    Hi domi25 ,

    Please try the following steps.

    Create two slicer table.

     

    Slicer1 = SUMMARIZE(Project,Project[Start Date])

Slicer2 = SUMMARIZE(Project,Project[Start Date])

     


    Put the Start Date field of both Slicer tables into both slicers.

    Slicer1 is used to select a date range and the slicer2 is used to select a specific date.

     

    Then create a measure.

     

    Measure = 
IF(SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer1[Start Date]) || SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer2[Start Date]),1,0)

     

     

    Then place this measure in the visual containing the data as well as in the filter, setting the condition to is 1

     

    Then you can see past and future projects.

     

     

     

     

     

     

     

     

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

dharmendars007's avatar
dharmendars007
Memorable Member
1 year ago

Hello domi25 , 

 

Please follow the below stesp.

 

Step1 - Please create calendar Table()

DateTable = CALENDAR(MIN(Projects[project start date]), MAX(Projects[project start date]))

 

Step2 - Use a DAX formula to create a new column that classifies the date into periods

Period =
IF('DateTable'[Date] < TODAY(), "Past",
IF(YEAR('DateTable'[Date]) = YEAR(TODAY()) && 'DateTable'[Date] >= TODAY(), "Current","Future"))

 

Step3 - Add a calculated column that formats the date into a "Month-Year" format

MonthYear = FORMAT('DateTable'[Date], "MMM YYYY")

 

Step4 - Link your date table to your Project table by dates.

 

Step5 - In your Power BI report, use slicers for both Period and Month + Year from the datetable to filter the data you want

 

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

 

Thank You

Dharmendar S

LinkedIN 

domi25's avatar
domi25
Frequent Visitor
1 year ago

The solution you presented works exactly like mine. What I meant was an answer to the question of whether the kind of filtering I want is possible. I want to be able to select (in your solution) 'FUTURE' in one slicer and 'January 2023' in another, and I want to see all those projects. However, with this solution, they are mutually exclusive, and records not appear because January 2023 is not in the future. What I want is to see all future projects and that one from the past."

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for dharmendars007 's reply ,please allow me to provide another insight.
    Hi domi25 ,

    Please try the following steps.

    Create two slicer table.

     

    Slicer1 = SUMMARIZE(Project,Project[Start Date])

Slicer2 = SUMMARIZE(Project,Project[Start Date])

     


    Put the Start Date field of both Slicer tables into both slicers.

    Slicer1 is used to select a date range and the slicer2 is used to select a specific date.

     

    Then create a measure.

     

    Measure = 
IF(SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer1[Start Date]) || SELECTEDVALUE(Project[Start Date]) IN VALUES(Slicer2[Start Date]),1,0)

     

     

    Then place this measure in the visual containing the data as well as in the filter, setting the condition to is 1

     

    Then you can see past and future projects.

     

     

     

     

     

     

     

     

     

    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.