Multi-select criteria on timekey / auto-exist issue

DAX DAX Calculations
1

hi,
ref to pbix file here.

i’m trying to get the correct totals for number of

  1. planned arrivals

  2. planned departures

  3. actual arrivals

  4. actual departures

  5. combined totals for planned arrivals + planned departures (summation of 1 & 2 above)

  6. combined totals for actual arrivals + actual departures (summation of 3 & 4 above)

based on the selected hourly slicer/filter.

however, i think i hv run into the issue of auto exist which i hv tried to resolve using separate dimension files for the slicer / filter resulting the wrong totals for all.

i’d appreciate any help to learn how to resolve this.

tks, -nik

1 Like
2

Hi @nikahafiz,
Thank you very much for posting your query in the forum.
You can modify the ATAATD and STDSTA measurements as:

ATAATD =
CALCULATE(
     [Departures-Actual-Hr],
     ALL( 'ATA.HR'[ATA.HR] )
     )
+
CALCULATE(
     [Arrivals-Actual-Hr],
     ALL( 'ATD.HR'[ATD.HR] )
     )

STDSTA =
CALCULATE(
     [Departures-Planned-Hr],
     ALL( 'STA.HR'[STA.HR])
     )
+
CALCULATE(
     [Arrivals-Planned-Hr],
     ALL( 'STD.HR'[STD.HR])
     )

Regards
211116.sample_Auto_Exist_JAFP.pbix (5.5 MB)

3

Hi @nikahafiz, did the response provided by @jafernandezpuga help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hi @nikahafiz, we noticed that no response was received from you with regard to our post last Oct 18th.

We just want to check if you still need further help with this post.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hello @nikahafiz it’s been a while since we got a response from you. In case there won’t be any activity on it by Friday, Nov. 4, we’ll be tagging this post as Solved.

Thank you!

6

hi @jafernandezpuga,

many thanks for your reply.
i’m also sorry for the late reply as i was on the road while trying to complete a related project.

while we have solved the issue for items 5 & 6 of my earlier email, we still have not found the answers for items 1-4 which i believe is caused by the auto exist element. kindly refer to following samples

  1. sta-std

planned departures shud b 16 (instead of 11)
planned arrivals shud b 8 (instead of 5)

  1. ata-atd

actual departures shud b 5 (instead of 3)

i hope we can find a solution to that.

tks & krgds, -nik

7

Hola @nikahafiz,
According to the measure used, it is filtering by origin = “JFK”
Departures-Current =
CALCULATE(
COUNTROWS( JFK ),
JFK[ORIGIN] = “JFK”,
JFK[ATD] <> BLANK()
)
There are only 11 departures from JFK and 5 arrivals at JFK.

8

Hi @nikahafiz ,

I’d like to check again if the answer @jafernandezpuga provided above helped you solve your inquiry.

If it does, kindly mark the answer as the solution that solved your query.

If not, how far did you get, and what kind of help do you need further?

9

Hello @nikahafiz , did the response above help solve your query? It’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

10

hola @jafernandezpuga ,

indeed you are right!
the calculated results were correct for the counts of std & sta for the given example.

however, do u know why do the table visuals produce the ‘extra’/wrong info & how can we resolve that to show the correct list i.e. 11 & 5 flight info respectively for the std & sta for the selected date & time-range?

krgds, -nik

11

Hi @nikahafiz,
I have modified the measures so that the rest of the filters on the JFK table are not eliminated:

Arrivals-Actual =

CALCULATE(

    COUNTROWS( JFK ),

    KEEPFILTERS(JFK[DEST] = "JFK"),

    KEEPFILTERS(JFK[ATA] <> BLANK())

    )

Arrivals-Planned =

CALCULATE(

    COUNTROWS( JFK ),

    KEEPFILTERS(JFK[DEST] = "JFK")

    )

Departures-Actual =

CALCULATE(

    COUNTROWS( JFK ),

    KEEPFILTERS(JFK[ORIGIN] = "JFK"),

    KEEPFILTERS(JFK[ATD] <> BLANK())

    )

Departures-Planned =

CALCULATE(

    COUNTROWS( JFK ),

    KEEPFILTERS(JFK[ORIGIN] = "JFK")

    )

To each table we add the corresponding measure that is not blank as a filter.

image
image894×542 32.6 KB

Regards,

211116.sample_Auto_Exist_JAFP.pbix (5.5 MB)

1 Like
12

hola @jafernandezpuga,
many tks again & appreciate yr helps always.
krgds, -nik

1 Like