Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Antonio195754
Helper IV
Helper IV

If statement causing blanks when changing Date filter

Hi, 

I have a filter problem that I beleive is due to the below DAX I use to calculate % complete of given courses.  What is happening is we get month over month results in, but periodically, we get new courses added to the mix.  So when I filter for May with the 10 courses below as part of my DAX, everything works great.  But when I go to a previous month, say April, where there was only 8 courses, every visual in one way or another that uses the below DAX, will all goes blank.  I think it's because i'm saying below "if you see this course and this course and this course etc, complete, then complete else open.  But because it doesn't find the new courses from the new month when I change the month filter, it returns blank.  Is there a way to modify the below to say something like, if ANY of these courses show complete at any given month, then return complete else open.  End of day i'm simply trying to get my data to change as i change my Date filter.

 

All Programs Complete/Open Column =
IF([Course 1] = "Complete"
&& [Course 2] = "Complete"
&& [Course 3] = "Complete"
&& [Course 4] = "Complete"
&& [Course 5] = "Complete"
&& [Course 6] = "Complete"
&& [Course 7] = "Complete"
&& [Course 8] = "Complete"
&& [Course 9] = "Complete"
&& [Course 10] ="Complete","Complete","Open")
 
All Programs Complete =
CALCULATE(
    COUNTA('2021 Training'[All Programs Complete/Open Column]),
    '2021 Training'[All Programs Complete/Open Column]
        IN { "Complete" }
)
 
% Complete =
DIVIDE(
    [All Programs Complete],
    COUNTA('2021 Training'[Employee Count])
)
1 ACCEPTED SOLUTION

@Antonio195754 at this point, it is easier to paste some sample data and expected output otherwise we will keep going in circles. Better if you create a sample pbix file with the expected output, it will help others to quickly assist.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@Antonio195754 so you need to change your condition to OR instead of AND which you are currently doing. I think that is what you are looking for, to change it to OR just replace && with || and then check.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I misspoke parry2k, what you recommended certainly does return what i thought i was looking for but what that then does is say, if this course OR any of the next courses are complete then return complete.  That's not what i'm trying to solve for, i apologize for the confusion.  But that did help me confirm it's definitely the IF statement below that is causing everything to break when i go to a previous month b/c when i do the following, where i add the OR || to the courses that were introduced in May, when i change to the April filter, the data is showing up now;  It's not the correct data b/c i'm telling it below if these courses OR either of these 2 new courses are complete, then return complete.  But at least it helps me confirm the problem.  How do i write the below statement so that it captures the courses during the given month.  Can it be done with one formula?  Example in April there were 8 courses i need to get completion % in, and in may there are now 10 courses i need to get complete % in.  But i want to be able to switch from one month to another with my date filter.

 

All Programs Complete/Open Column =
IF([Course 1] = "Complete"
&& [Course 2] = "Complete"
&& [Course 3] = "Complete"
&& [Course 4] = "Complete"
&& [Course 5] = "Complete"
&& [Course 6] = "Complete"
&& [Course 7] = "Complete"
&& [Course 8] = "Complete"
|| [Course 9] = "Complete"
|| [Course 10] ="Complete","Complete","Open")

 

 

@Antonio195754 at this point, it is easier to paste some sample data and expected output otherwise we will keep going in circles. Better if you create a sample pbix file with the expected output, it will help others to quickly assist.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hey @parry2k my fault for not replying back but we figured out a plan to make it work in the backend data.  All good!  I do have another question i just posted maybe you can help.  It's along the same lines of this project.  Thanks again

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.