2 Date ranges with 1 date used to filter data table conditionally.

[NeverGiveUp]

Date Selected						
2/28/2023						
						
						
						
Data Table				Condition 1	Condition 2	Both Conditions Met
CODE	Stage 1	Stage 5		Stage 1 < = Date Selected	Stage 5> Date Selected	Will it show in the table?
1111111	1/5/2001	8/9/2021		TRUE	FALSE	NO
222222	2/5/2018	9/1/2021		TRUE	FALSE	NO
33333	3/5/2023	5/2/2024		FALSE	TRUE	NO
44444	5/1/2021	8/1/2021		TRUE	FALSE	NO
555555	2/8/2023	8/7/2025		TRUE	TRUE	YES
66666	6/8/2023	8/9/2027		FALSE	TRUE	NO
777777	9/1/2002	2/1/2024		TRUE	TRUE	YES
8888888	9/2/2024	8/5/2025		FALSE	TRUE	NO

*I apologize as I am unable to provide my file due to work restrictions.

Hello I have 2 different date ranges with 2 different min & max dates.

stage 1 min - max - only 1 date applies to each code

stage 5 min - max - only 1 date applies to each code

Goal is to have 1 date picker available to select, defaulted to today's date.

User can select a different date past or future.

Based on whatever date is selected the data table will filter with only BOTH conditions that are true.

Conditions =

 (stage 1 date range) <= date selected 

 (stage 5 date range) > date selected 

For example.

Can someone please help me?  I have tried multiple ways to do this, but it is not liking it as the temporary data stores on my data seems to be overload.   And since the dates can vary so much, the performance is not well.

PS - I am only providing below as it works - however, I am wanting to scratch these solutions altogether if possible and find a better way so that I am not getting errors due to data overload with the temporary measures/calculated columns, etc.

*I think they work, I didn't test thoroughly due to performance issues.  But the ones I tested did work.

this works to evaluate the stage 1 date and give me the default today date:

Dates_Filter =

UNION(ADDCOLUMNS(DATESBETWEEN('Calendar Custom'[Date],

MIN('Calendar Custom'[Date]), TODAY()),"Type", "Default"),

ADDCOLUMNS(CALENDAR(MIN('MERGE NON AGG fabric home portfolio forecast'[stage1date]),

MAX('MERGE NON AGG fabric home portfolio forecast'[stage5date])), "Type", "Custom"))

This works (when applied to the data table as a filter) to give me the stage 5 conditions.

Stage5 filter NEW =

VAR _Isactive =

    CALCULATE(

        MAX('MERGE NON AGG fabric home portfolio forecast'[stage5date]),

        USERELATIONSHIP( 'Dates_Filter'[Date] , 'MERGE NON AGG fabric home portfolio forecast'[stage5date] )

    )

this also works for stage 5 - 

MYMax =

MAXX(

    FILTER(

        SELECTCOLUMNS(

            ALLSELECTED('Dates_Filter'[Date]),

            "id", 'MERGE NON AGG fabric home portfolio forecast'[MergeStage5NEW],

            "max", MAXX(ALLSELECTED('Dates_Filter'[Date]), 'Dates_Filter'[Date])

        ),

        [id] = MAX('MERGE NON AGG fabric home portfolio forecast'[stage5date])

    ),

    [max]

)

MergeStage5NEW =

var _SelectedValue = MAX('MERGE NON AGG fabric home portfolio forecast'[stage5date])

RETURN _SelectedValue

Stage5 filter NEW =

VAR _filtervalue = [MYMax]

VAR _stage5 = [MergeStage5NEW]

var _result =

    SWITCH(

        TRUE(),

        _stage5 >= _filtervalue,

         2,

        _stage5 < _filtervalue,

        3,

        ISBLANK (_filtervalue),

         _stage5,

         BLANK()

    )

RETURN

_result

return

IF ( ISBLANK(_Isactive) , "Keep" , "Hide")

[bolfri]

Will you prodive some sample rows to work with or do you want to show you the solution on any random data?

[NeverGiveUp]

Hello, I provided some sample data to use with expected outcome.  Hope this helps!

[bolfri · Accepted solution]

With the given SampeData

Step 1) I've created a dim_calendar that is not connected by any relationship with my table. I will use a new date field as a filter. I only change a new created column as a date type.

dim_calendar = CALENDAR( MIN(FIRSTDATE(SampleData[Stage 1]),FIRSTDATE(SampleData[Stage 5])), MAX(LASTDATE(SampleData[Stage 1]),LASTDATE(SampleData[Stage 5])) )

Step 2) Create a measure that checks all conditions.

Conditions met = var selected_date = lastdate(dim_calendar[Date]) return SUMX(FILTER(SampleData,SampleData[Stage 1] <= selected_date && SampleData[Stage 5] > selected_date),1)

Results:

[NeverGiveUp]

Hi, so I just saw that in my data, sometimes there may be more than one of the same code due to either a different stage 1 date, or a different stage 5 date.  I realize this is different than what I originally mentioned.  Lots of rows of data.  My apologies as I understand this now brings a layer of complexity.  ***FOR THIS, I will write the min/max in my SQL query to bring in as only one date.***

However, it requires me to have the date fields on the actual table I am filtering. 

Is there a way around this? 

In other words, I am not wanting these dates to show, how can I prevent this?  

I tried renaming and resizing to try to fake hide it but dates in the values are too wide.

I read that the Modeling tab in Power BI and then New Parameter from the May 2022 release.  But I am not familiar with how I would do this.

[bolfri]

Can you provide a new sample data with such problem and expected results that you want? I think I don't understand what the problem is 🙂

[NeverGiveUp]

I think I got it working.  But now I have a new problem :).   Although I am hiding the rows in the data table now, all my visuals are still showing as if there is data for those that are hidden in the table.  I am looking this up now to see how to handle.  But if anyone knows already, please share :).  Thank you so much for your help earlier @bolfri !!!!

[bolfri]

You need to filter out the data in Power Query M editor, not the "Data" tab. Filtering on the Data tab won't affect the visuals.

[NeverGiveUp]

Got it!  

FPCCountMeasure =

var FilteredTable = FILTER('MERGE NON AGG fabric home portfolio forecast', [Conditions met] <> BLANK())

var selected_date = LASTDATE(dim_calendar[Date])

return

CALCULATE(DISTINCTCOUNT('MERGE NON AGG fabric home portfolio forecast'[Finished_Product_Code]), FilteredTable, selected_date)