Measure to count by min / max date range while ignoring slicer

[SHullen]

Hi, I have a measure that counts program completions.  It works but only for selected terms.  I need it to include the terms between the two selected terms based on the start and end dates of the selected terms.  In this example, Spring 2023 and Summer 2023 need to be included in the count.  Thanks!  UPDATE: The problem with my example is that it only has records for the terms that I want to count.  But if you look at the slicer, you'll see that it goes from Fall 2020 to Fall 2024.  If I use any version of All() it counts everything.  Without All() it only includes the selected terms in the slicer.  That is why I'm trying to use the Min(Term_Start_Date) & Max(Term_End_Date) from the slicer to count everything between those dates.  Thanks!!

Term	StudentID	Program Code	Term_Start_Date	Term_End_Date	Completion_Date
Fall 2022	11111	Prog_1	9/5/2022	12/31/2022	8/1/2022
Fall 2022	11111	Prog_2	9/5/2022	12/31/2022
Fall 2022	22222	Prog_3	9/5/2022	12/31/2022	10/1/2022
Fall 2022	22222	Prog_4	9/5/2022	12/31/2022
Fall 2022	33333	Prog_5	9/5/2022	12/31/2022
Spring 2023	11111	Prog_6	1/1/2023	5/15/2023	3/1/2023
Spring 2023	11111	Prog_7	1/1/2023	5/15/2023
Spring 2023	22222	Prog_8	1/1/2023	5/15/2023
Spring 2023	22222	Prog_9	1/1/2023	5/15/2023	2/2/2023
Spring 2023	33333	Prog_10	1/1/2023	5/15/2023
Summer 2023	11111	Prog_11	5/20/2023	9/2/2023
Summer 2023	11111	Prog_12	5/20/2023	9/2/2023	6/2/2003
Summer 2023	22222	Prog_13	5/20/2023	9/2/2023
Summer 2023	22222	Prog_14	5/20/2023	9/2/2023	10/1/2003
Summer 2023	33333	Prog_15	5/20/2023	9/2/2023
Fall 2023	11111	Prog_16	9/8/2003	12/31/2023
Fall 2023	11111	Prog_17	9/8/2003	12/31/2023	11/1/2023
Fall 2023	22222	Prog_18	9/8/2003	12/31/2023
Fall 2023	22222	Prog_19	9/8/2003	12/31/2023	12/1/2023
Fall 2023	33333	Prog_20	9/8/2003	12/31/2023	2/2/2024

Here's my DAX code which either counts all records or the two fall terms if I get rid of all.

#_Comp_Awd_Term_All = CALCULATE(COUNTROWS( FILTER(GROUPBY( 'dw vw_Exec_Course_Performance','dw vw_Exec_Course_Performance'[StudentID],

'dw vw_Exec_Course_Performance'[ProgramCode],'dw vw_Exec_Course_Performance'[Term]  ,'dw vw_Exec_Course_Performance'[ConferralDate]),

'dw vw_Exec_Course_Performance'[ConferralDate] >= MIN('dw vw_Exec_Course_Performance'[AP_Start_Dt]) &&

'dw vw_Exec_Course_Performance'[ConferralDate] <= MAX('dw vw_Exec_Course_Performance'[AP_End_Dt]) )),

ALL())

[SHullen · Accepted solution]

I figured this out based on another post I came across.  Calculate the min and max variables first using the slicer values.  Then filter ALL() with the min and max variables.  Works as expected!!

#_Comp_Awd_Term_All =

var _max = MAX('dw vw_Exec_Course_Performance'[AP_End_Dt])

var _min = min('dw vw_Exec_Course_Performance'[AP_End_Dt])

RETURN

CALCULATE(

    CALCULATE (

        COUNTROWS (

            FILTER (

                GROUPBY (

                    'dw vw_Exec_Course_Performance',

                    'dw vw_Exec_Course_Performance'[StudentID],

                   'dw vw_Exec_Course_Performance'[ProgramCode],

 --                   'dw vw_Exec_Course_Performance'[Term],

                   'dw vw_Exec_Course_Performance'[ConferralDate]

                ),

                'dw vw_Exec_Course_Performance'[ConferralDate]

                    >=  ( _min )

                    && 'dw vw_Exec_Course_Performance'[ConferralDate]

                        <= _max)

            )

        )  , ALL('dw vw_Exec_Course_Performance'[Term],'dw vw_Exec_Course_Performance'[Conf_Friendly]   )

)

[SHullen]

Hi  Yilong Zhou,

 Thanks for taking the time to look into this issue.  Your solution doesn't include spring and summer in the final result.  I want the DAX to use the min start date and max end date as a range and ignore the seleted terms in the slicer.  So there would be 7 counted in the final results instead of 3.

[v-yilong-msft]

Hi @SHullen ,

I create a table as you mentioned.

Then I change the DAX codes and here is the updated DAX codes.

#_Comp_Awd_Term_All =
VAR _A =
    CALCULATE (
        COUNTROWS (
            FILTER (
                GROUPBY (
                    'dw vw_Exec_Course_Performance',
                    'dw vw_Exec_Course_Performance'[StudentID],
                    'dw vw_Exec_Course_Performance'[Program Code],
                    'dw vw_Exec_Course_Performance'[Term],
                    'dw vw_Exec_Course_Performance'[Completion_Date]
                ),
                'dw vw_Exec_Course_Performance'[Completion_Date]
                    >= MIN ( 'dw vw_Exec_Course_Performance'[Term_Start_Date] )
                    && 'dw vw_Exec_Course_Performance'[Completion_Date]
                        <= MAX ( 'dw vw_Exec_Course_Performance'[Term_End_Date] )
            )
        ),
        ALLSELECTED ( 'dw vw_Exec_Course_Performance'[Term] )
    )
RETURN
    IF ( _A, "Count", "Out" )

Measure = 
IF (
    'dw vw_Exec_Course_Performance'[#_Comp_Awd_Term_All] = "Out"
        && SELECTEDVALUE ( 'dw vw_Exec_Course_Performance'[Completion_Date] ) = BLANK (),
    BLANK (),
    'dw vw_Exec_Course_Performance'[#_Comp_Awd_Term_All]
)

Fianlly when I select the slicer, it will give me the things you want.

Best Regards

Yilong Zhou

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