Forum Discussion

meredink91's avatar
meredink91
Frequent Visitor
2 years ago

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

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 spec...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    meredink91
    Frequent Visitor
    2 years ago

    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's avatar
      meredink91
      Frequent Visitor
      2 years ago

      Hi @v-huijiey-msft

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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!