Issue with Filtering out Certain Data from a Date Table Using DAX

[meredink91]

Hello,

Please let me know if any additional info is needed as I'm still a novice at PBI and DAX. 

MY issue is that I have a dashboard set up for a client who wants to track who is taking specific courses. I have 2 tables set up for this including a table with all course names from the LMS and the dates taken, and I also I have date table I've created with DAX to calculate specify month, year, quarter they were completed. THe issue comes in where now the client has added another curriculum with 5 additional courses they want to track, when I filter those in, it retroactively adds the learning hours and I just want to see learning hours after March 31 2024 (when the curruculum was added) with the new courses and already tracked courses. 

Below is the DAX for the date table I was already able to come up with and reference the course table ('Learner Course Activity Detail Report'). Can someone please help me to understand what I am doing wrong as whenever I test it it still includes the course hours before April 2024 and want the specific course data to show up afterwards. Thanks so much for your help and just let me know if any additional info is needed!

DateT After Apr 2024 =

VAR MinYear = YEAR(DATE(2022, 01, 01))

VAR MaxYear = YEAR(TODAY())

VAR SpecificCourses = {

    "Time Management Fundamentals",

    "Getting Things Done",

    "How to Set Goals When Everything Feels Like a Priority",

    "How to Regain Control of Your Time, Energy, and Priorities",

    "Secrets of Effective Prioritization"

}

RETURN

ADDCOLUMNS(

    FILTER(

        CALENDARAUTO(),

        AND(YEAR([Date]) >= MinYear, YEAR([Date]) <= MaxYear)

    ),

    "Calendar Year", "CY " & YEAR([Date]),

    "Month Name", FORMAT([Date], "mmmm"),

    "Month Number", MONTH([Date]),

    "Month Year", FORMAT([Date], "mmm yyyy"),

    "Month year Sort", FORMAT([Date], "YYYYMM"),

    "Calendar Quarter/Year", QUARTER([Date]) & "/" & YEAR([Date]),

    "Course Name",

        IF([Date] <= DATE(2024, 03, 31),

            // Courses before April 2024, excluding specific courses

            CALCULATE(

                MAX('Learner Course Activity Detail Report'[name]),

                FILTER(

                    'Learner Course Activity Detail Report',

                    'Learner Course Activity Detail Report'[week] <= [Date] &&

                    NOT 'Learner Course Activity Detail Report'[name] IN SpecificCourses

                )

            ),

            // Courses from April 2024 onwards, including all courses

            CALCULATE(

                MAX('Learner Course Activity Detail Report'[name]),

                FILTER(

                    'Learner Course Activity Detail Report',

                    'Learner Course Activity Detail Report'[week] <= [Date] &&

                    (

                        'Learner Course Activity Detail Report'[name] IN SpecificCourses  

                    )

                )

            )

        )

)

[Anonymous]

Hi @meredink91 ,

Just to confirm, do you have a separate table for dates? It needs to contain the date intervals of your data table, and you need to disconnect between the two tables if you are using dates for filtering.

Please try:

You can modify the Measure syntax as follows:

Measure =
VAR SpecificCourses = {
    "Time Management Fundamentals",
    "Getting Things Done",
    "How to Set Goals When Everything Feels Like a Priority",
    "How to Regain Control of Your Time, Energy, and Priorities",
    "Secrets of Effective Prioritization"
}
RETURN
    IF (
        MAX ( 'Learner Course Activity Detail Report'[Date] ) > DATE ( 2024, 03, 31 ),
        CALCULATE (
            MAX ( 'Learner Course Activity Detail Report'[name] ),
            FILTER (
                'Learner Course Activity Detail Report',
                'Learner Course Activity Detail Report'[name] IN SpecificCourses
            )
        ),
        MAX ( 'Learner Course Activity Detail Report'[name] )
    )

Try this modified syntax, and if this doesn't help you solve the problem.  I would be grateful if you could provide me with the pbix file or sample data.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[meredink91]

Hello @Anonymous , thanks so much for your reply!

Yes, I have a separate table for Dates. What do you mean that I have to disconnect the 2 tables (is this in reference to their relation for data modelling)?

Also, should add this just as a measure in one of the tables, or is this something to add to the table formula I've provided in my initial post?

Thanks!

[meredink91]

Hi @v-huijiey-msft

Kindly following up on my previous question. If you can let me would be just appreciated. Thanks!

[Anonymous]

Hi @meredink91 ,

Yes, if you use dates for filtering, you need to disconnect the join between the two tables.

Try my syntax and if that doesn't help you solve the problem. I would appreciate it if you could provide me with the pbix file or sample data.

Please remember to delete sensitive data and do not log into your account in Power BI Desktop when uploading pbix files.

If you have any other questions, please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[meredink91]

Thanks @Anonymous - I did try to the syntax and still not working. How do I send you a sample pbix file through here?

[meredink91]

Also, and maybe irrelevent, but I do have a a page level filter for the specific names of the courses to include as the list of courses from LMS is large. Might not make a difference but thought I'd mention