Forum Discussion
multi-select criteria on timekey
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
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
Most Valuable ProfessionalHey 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
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