cancel
Showing results for
Did you mean:
Helper III

## 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
Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

4 REPLIES 4
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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper III

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")

Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper III

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors