multi-select criteria on timekey

[Anonymous]

hi,

ref pbix file here.

individually, i'm able to get the followings:

a. total number of planned departures - based on STD
b. total number of planned arrivals - based on STA
c. total number of actual departures - based on ATD
d. total number of actual arrivals - based on ATA

however, i'm unable to get the followings:

(1) combined total for planned departures & planned arrivals based on same STD and STA
(2) combined total for actual departures & actual arrivals based on same ATD and ATA

i'd appreciate help to solving the requirements in (1) & (2) above.

p.s.

@jdbuchanan71,

this is also related to my previous query.

[selimovd]

Hey @Anonymous ,

when you were able to create the single items, you can just add the measures up to get the total:

Total Planned = [planned departures] + [planned arrivals]

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

thanks for your reply, @selimovd.

i'm still unable to get the correct combined totals (of planned departures + planned arrivals) if i were to select the same STD.HR  (scheduled time of departure hour) & STA.HR (scheduled time of arrival hour). 

as an example, if u were to look at the 'DEP-HR' tab, the total planned departures at 06:00 STD.HR is 6 & the total planned arrivals at 06:00 STA.HR is 4. thus, the correct combined totals at 06:00 STD.HR & 06:00 STA.HR shud b 6+4 or 10.

for actual departures & actual arrivals, for 06:00 ATD.HR & 06:00 ATA.HR, the combined totals shud be 4+2 = 6.

hope i can get a solution to this.

tks & krgds, -nik

[selimovd]

Hey @Anonymous ,

OK, I think I understood your problem.

This happens because you change the filter interaction of your slicers. For example, the STD.HR slicer is affecting the planned departures, but not the actual departures:

And the same for ATD.Hour, that is not affecting the planned departures and is affecting the actual departures:

For that reason it doesn't work to only sum up the 2 measures because you also have to recreate that interaction. You can do that with DAX and ignoring specific slicers with the ALL function.

In this case the following measure is doing that:

Total Departures =
CALCULATE (
    [Departures-Planned-Hr],
    ALL ( JFK[ATD.HR] )
)
+
CALCULATE (
    [Departures-Actual-Hr],
    ALL ( JFK[STD.HR] )
)

And then also the result is adding up correctly:

You can use the same logic for the arrivals.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

many thanks again, @selimovd.

i have redone the calculations to reflect the following hourly stats:

(1) combined planned departures + actual departures
    (ref to STDATD tab - just to check for correct calculations | working!)

(2) combined planned arrivals + actual arrivals
    (ref to STAATA tab - just to check for correct calculations | working!)

(3) combined planned arrivals + planned departures
    (ref to xSTASTD tab - this is needed for my study | not working!)

(4) combined actual arrivals + actual departures
    (ref to ATAATD tab - this is needed for my study | working!)

can u kindly check & help to resolve the issue with the calculation for the combined planned arrivals + planned departures (in the STASTD tab)?

u'll find the updated pbix here.

tks & krgds, -nik

[selimovd · Accepted solution]

Hey @Anonymous ,

I found the problem for the calculation. But I have to explain a little bit what happened here.

In general in Business Intelligence you use dimensional tables to filter and fact tables for the calculation. This means master data like date would be in a dimensional table, time would be in a dimensional table, Carrier would be in a dimensional table. You actually did that pretty good for most cases, but the STA.HR and STD.HR are still in the fact table and not in a dimensional table.

For that reason you filter for STA.HR and STD.HR in the fact table and also the calculation like the measure [Arrivals-Planned] is calculated on the fact table. When you filter multiple columns and calculate on the fact table, a phenomena called auto-exist can happen and is what happened here. Auto-exist is a optimization technique to avoid unnecessary calculations. In this case the value is not calculated properly, the engine is using the cached results because it thinks it doesn't have to re-calculate that value. But in this case this is just wrong, it should have re-calculated the measure.

You have two possibilities to fix that.

1. In my opinion the better one is to improve the data model by using proper dimensional tables. I created two new tables for STA.HR and STD.HR and used them for the filtering and the calculation. Like this the calculation is correct. You can also use your TimeTable with USERELATIONSHIP, but then the STA.HR and STD.HR time would always be identical. I attached my quick and dirty example file to this post.

2. You could try to re-create the measures with the CALCULATETABLE function. There is not auto exist with that function, so the calculation would be correct, but you have to calulate everything manually. In the future then you would have the same problem again.

So it's up to you what you want to do.

You will find more details about auto exist in the following article by our experts from SQLBI:

Understanding DAX Auto-Exist - SQLBI

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

thank you sooo much, @selimovd.

i must admit that i was drowned in so many calculations that i have forgotten the basics of fact + dimensional tables in data modelling.

many thanks to you again & krgds, -nik

[selimovd]

Hey @Anonymous ,

your data model looked pretty good. Just this detail brought you to this situation.

But I'm pretty sure you will be able to fix it. Otherwise just let me know.

Best regards

Denis