Hello my friends today were answering a question
that came in from one of our Learn Power BI family members, Venkatesh.
And.
This. Is what Venkatesh is asking and I'll show
you exactly what he's what he's talking about.
So he has a dashboard with slicers filtering
On two date fields right.
But regardless of what the filters are set to there are his some data which has for the
starting an end date.
It's it's blank so the date values are blank.
And he always wants to include that.
In the filters no matter what the filter selection is so right.
So let me show you so the closest I could
get to that scenario in adventure works.
Was that.
I just use a single the order date.
And now in this case we do have.
Order date.
I'm gonna show you the data set where I hacked together in the in the query editor and I
made a blank for some set of dates you can see here.
This is the order date and it's.
And is blank for for and you know the records
which add up to one point nine million dollars and is there every every time now.
SO if I understood make it is correctly what
he wants is doesn't matter what the selection in the day table is the blank should.
Always be shown.
So in fact imagine if our if we had if the
if the user selected two thousand sixteen.
And they.
Also.
Selected blank. Speaker 0:
Sue the blanket should always be included and they should be reflected in the data that
he's showing elsewhere so again this stable is just the four debug purposes I don't think
that's the that's what's being shown on his report.
Speaker 1: I'm gonna make that assumption.
Speaker 0: So he's showing some other information and
in this case I picked like to see is by category. Speaker 1:
And sees about occupation. Speaker 0:
But in both of these numbers. Speaker 0:
Even when they select just two thousand sixteen we want to include.
Speaker 1: The blank one as well and you can see that.
Speaker 0: Including blanket on sixteen plus bank blank
is sixteen point four million. Speaker 1:
And that's what's being. Speaker 0:
Reflected over here but again we don't want the user to do this.
Speaker 1: Clumsy thing of select this and then hold
on control can cite blank right so we want to make it.
Speaker 0: Easy for them and just say up you know it
doesn't matter what you select will. Speaker 1:
Always gonna including show blank and I'm sure make Asia the valid.
Speaker 0: Scenario for that so let's zero in on this
two thousand sixteen and we gonna see if we can if we can create something so that.
Speaker 1: As.
Speaker 0: Even though the user selects two thousand
sixteen. Speaker 1:
The blank is always included and we get the story was sixteen point four seven three.
Speaker 0: Without them having to actually.
Speaker 1: Click the blank on someone a.
Speaker 0: Duplicate that page and this call is to.
Speaker 1: Teach to why not.
Speaker 0: And here.
Speaker 1: I'm gonna on select blank.
Speaker 1: Let's just acted on sixteen.
Speaker 0: But the the total we're going for.
Speaker 1: Is that sixteen I've forgotten it already.
Speaker 0: Sixteen point four.
Speaker 1: Seven three so I'm gonna.
Speaker 0: Come in here and I'm gonna remove this.
Speaker 1: Year month and I don't need any of these actually
so let's just keep the the sales number. Speaker 0:
All right so we're on beach to this is what I want to be working.
Speaker 1: And.
Speaker 0: I want to I want something that turns me always.
Speaker 1: Including the blank.
Speaker 0: So let's go ahead and.
Speaker 1: And write a measure and would.
Speaker 0: Probably try to do it step by step so let's
just say new measure. Speaker 0:
And we gonna go over here and say creates sales.
Speaker 1: Are.
Speaker 0: Always include.
Speaker 1: Blank date.
Speaker 1: And we're gonna call W. I. T. work in progress
now because we're still working on it. Speaker 0:
So I'm gonna say our calculate let's let's just first see if we can calculate sales just
for the blind dates so what's it calculates sales and this one is simple I'll just say.
Speaker 1: Calendar date.
Speaker 1: Our.
Speaker 0: Equals.
Speaker 1: Blank.
Speaker 1: All right so let's see.
Speaker 0: What is actually you know what.
Speaker 1: I'll just say for now I'll just say blank
dates. Speaker 1:
So this is what it's showing right now and on within the ad that here and notice here
that he does. Speaker 0:
What it say's. Speaker 1:
It. Speaker 0:
Always shows the blank tape doors are no matter what is selected we just.
Speaker 1: Get the blank day tori so it's always kinda
including that. Speaker 0:
So let's say I think we can do use two approaches from here.
Speaker 1: Astride boat.
Speaker 0: I saw I had I had a.
Speaker 1: Fancy approach in mind in fact that's the
first one that I've tried and but then I realize there's a simple approach as well so I'll
walk you through the same process so this is a fancy one first that oddly enough backing
to me our first that's the first when I taught off.
Speaker 0: So I I realize that that I have I can do this
and I can say I show me the sales for. Speaker 1:
With the calendar is blank and that's the filter context and I'm creating and filter
context in and when I specified this weight overrides any incoming filter context if you
want to learn more about dachshund filter context I do have a plea listening going to
make it in the description our our time on this video.
Speaker 0: So so what I wanted to create the context
which is a combination of whatever is being passed to me in the filter over here and.
Speaker 1: The blank dates right so.
Speaker 0: When I taught about that it felt like.
Speaker 1: I I need a union so that's that's the way
I went first so I'm gonna. Speaker 0:
I'm gonna grab this. Speaker 1:
This measure and creating new measure. Speaker 1:
And and we're gonna say sales and let's say. Speaker 1:
Always include blank dates and I'm hoping we can get it I saw the move this this one.
Speaker 0: So I'll say calculate sales.
Speaker 1: And.
Speaker 0: Then I wanted to do.
Speaker 1: Speaker 1:
A he. Speaker 0:
Union. Speaker 1:
Often. Speaker 0:
This with. Speaker 1:
The essentially that the calendar table that's coming in a whole lot of I'd write it out
kind of step by step to now of course this doesn't like me doing this calendar date.
Speaker 0: Equals plank.
Speaker 1: Because union expects a table.
Speaker 0: So how do we build.
Speaker 1: Our.
Speaker 0: Table.
Speaker 1: Which has done a blank tape blank one hand
and maybe there's an easier way to do that but I use this.
Speaker 0: Filter.
Speaker 1: Calendar and calendar date equals blank on.
Speaker 0: This.
Speaker 1: If you leave it this way is actually not gonna
work because we. Speaker 0:
Do we do need to clear. Speaker 1:
The incoming filter context as well so we're gonna see all calendar because the incoming
filter context doesn't have the blank dates right the incoming filter context just has
acted on sixteen and they're serving a say. Speaker 1:
All calendar and then zero and just to the county blank and union that with the calendar
table and now I think we just missing out a bracket and let's see holonomy see what's
going on with us weekly thing or I didn't have my brackets right so I think that's sorted
out so let's see how this works in action so we have now this new column let's see if
it if it shows me that sixteen. Speaker 0:
Point five. Speaker 1:
Eat it. Speaker 0:
Does sixteen point four seven three says giving me the right number.
Speaker 1: And and now I can.
Speaker 0: Use that.
Speaker 1: Instead of this CIOS measuring you so I can
go and I see always include the sales one and do the same thing over here remove that
one and add this new measure and it's always going to include the blank values and we gonna
test that in action as well. Speaker 0:
So here we have the number and so we can see two thousand sixteen B..
Speaker 1: Only selected to don sixteen we don't have
to go selected on sixteen and blank are and and but it's.
Speaker 0: Including that.
Speaker 1: And again this is just a.
Speaker 0: Working.
Speaker 1: As I call it so we would really.
Speaker 0: Show it so but.
Speaker 1: As you can see these graphs and tables whatever
was showing to always include the blank dates let's just test out one of their act.
Speaker 0: To what.
Speaker 1: Would happen if the selected two thousand
fifteen and blank so again I know basic measure the number we're looking for is seven point
seven one one two now if you go here again all we're gonna do is just acting thousand
fifteen and you can see the number is seven point seven one one so that's working.
Speaker 0: So that was the fancy approach but has I was
recording this video I revise that me there's a really really simple approach.
Speaker 1: Would use a size a little bit.
Speaker 1: So we can fit that and and show you that as
well. Speaker 0:
So so great so this is. Speaker 1:
One in. Speaker 1:
The. Speaker 0:
Other approach. Speaker 1:
That I believe you can use is are out and would you move that didn't fancy measure that
we built and. Speaker 0:
Also I love building things. Speaker 1:
In building blocks so I'd look at this and I realize that oops we.
Speaker 0: We have both building blocks what if you can
just do. Speaker 1:
A plus B. and and I think it does work. Speaker 0:
It's possible that there's some synergy that would break down I couldn't quite process
it in my head right away but misses try that so let's just try to simpler approach.
Speaker 1: And let's say sales.
Speaker 0: Sales.
Speaker 1: Always.
Speaker 0: Include.
Speaker 1: Blank and.
Speaker 0: Dates.
Speaker 1: And we'll call this out of the.
Speaker 0: Two and this would simply be.
Speaker 1: Like.
Speaker 0: This.
Speaker 1: Plus this so would say.
Speaker 0: Give me sales.
Speaker 1: And plus the CEOs with the blank dates to
write to it's it's doing. Speaker 0:
The blankets and I'm gonna take the W. I. P..
Speaker 1: Offer that other measure because I don't think
that's a work in progress anymore. Speaker 1:
Alright so let's try out. Speaker 0:
Our new measure V. two. Speaker 1:
And see how that does I'm gonna put V. two. Speaker 0:
Over here. Speaker 1:
And and you can see that it. Speaker 1:
It has the same result I'm gonna pause the video and just see if it works the same way
on these graphs and tables of I would believe it would.
Speaker 0: All right so I cleaned it up and now we have
the V. one of the solution and you can see. Speaker 1:
You can select two thousand fifteen seven point seven includes a blank two thousand
sixteen sixteen point four seven three it includes a blank.
Speaker 0: And we have a solution we too which was a
simpler approach. Speaker 1:
And it has the same shows the same numbers as well.
Speaker 0: Now of course I'm gonna up the challenge a
little bit so I operated. Speaker 1:
On an exemption and that's what data modeling is about you.
Speaker 0: You get to crossroads in their decision points.
Speaker 1: And there is options you makes a one big assumptions
that I made exemption that I made in this process was that the.
Speaker 0: Graphs being shown do not actually.
Speaker 1: Involve the date.
Speaker 0: Now this is crucial because if you did.
Speaker 1: Want to show something like this did want
to show the dates and show up at the blank dates.
Speaker 0: Dan.
Speaker 1: You you.
Speaker 0: I believe you cannot solve it.
Speaker 1: Using this measure approach.
Speaker 0: Because the slicer food there is.
Speaker 1: Out the blank dates so you're not.
Speaker 0: Able to display it.
Speaker 1: In that visual regardless of how you calculate
the measure go and give it a try and let me know what you find there right until next
time bar on my friend. Speaker 0:
Hey keep watching more videos. Speaker 1:
And keep learning bar B. I.. Speaker 0:
But if you did enjoy this video I would love to hear from you so leave a comment like subscribe
all the good stuff. Speaker 1:
Power on my friends.