cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

multi-select criteria on timekey

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.

1 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
 

View solution in original post

7 REPLIES 7
selimovd
Super User
Super User

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
 
Anonymous
Not applicable

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

 

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:

selimovd_0-1637141160550.png

 

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

selimovd_1-1637141211513.png

 

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:

selimovd_2-1637141443648.png

 

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
 
Anonymous
Not applicable

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

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
 
Anonymous
Not applicable

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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.