CALCULATE produces a blank result

[DAXRichArd]

Images:

AirportDAXRelationships = screen shot of my data model

Schedule = screen shot of the table this measure is being used in

Results = screen shot of the results I'm getting

DAX Functions / Expressions

Refer to "Results" screen shot

NOTE: All that follow are written as measure

TEST HAS Seats =
CALCULATE (
SUM ( 'Schedule Dynamic Table Report'[Seats] ),
ALL ( 'Schedule Dynamic Table Report'[Origin Code] ),
'Schedule Dynamic Table Report'[Origin Code] = "HOU",
'Schedule Dynamic Table Report'[Origin Code] = "IAH"
)

This produces blanks

TEST HOU Seats =
CALCULATE (
SUM ( 'Schedule Dynamic Table Report'[Seats] ),
ALL ( 'Schedule Dynamic Table Report'[Origin Code] ),
'Schedule Dynamic Table Report'[Origin Code] = "HOU"
)

This produces a correct result

TEST IAH Seats =
CALCULATE (
SUM ( 'Schedule Dynamic Table Report'[Seats] ),
ALL ( 'Schedule Dynamic Table Report'[Origin Code] ),
'Schedule Dynamic Table Report'[Origin Code] = "IAH"
)

This produces a correct result

TEST IAH no ALL Seats =
CALCULATE (
SUM ( 'Schedule Dynamic Table Report'[Seats] ),
'Schedule Dynamic Table Report'[Origin Code] = "IAH"
)

This produces a correct result.  Here I removed ALL (I initially added ALL to solve my problem then tested it by removing all).

Software Platform

PowerBI Desktop

SUMMARY

I work with commercial airline data. A characteriestic of this data is that it has geographic direction. Simplified, there is out bound or departure data, and there is in bound or arrival data. Depending on what I'm trying to analyze, I look at one direction, or the other, or both combined.

Problem Statement

• I want to analyze departure seats for two airports: 1) IAH, and 2) HOU. Both airports are within the metro city of Houston, Texas.
• I want to analyze them separately and combined (how do each perform independantly , and how do the City's combined airports perform?).
• The quantity I'm analyzing is 'seats' (seats are a measure of inventory (each seat can sell for 'x' (a.k.a. air fare).

Partial Solution

• When I use the CALCULATE fuction and filter for one airport code, the DAX code works and provides a correct result for what I intend it to do.
• When I write the same code but add a second filter (i.e. filter for IAH and filter for HOU), I get a blank result. Here the formula bar accepts my input and does not return an error message.
  • Refer to the first formula 'TEST HAS Seats'.

Thank you in advance for your help!

DAXRichard

Houston, Texas

[MFelix · Accepted solution]

Hi @DAXRichArd,

One thing to keep in mind when using the filter in the CALCULATE function is that it makes the AND option so when you are calculating both "HOU" / "IAH" the filter is looking for Origin codes that have both values at once you need to chan ge this to an OR, in this case the measure should look like this:

TEST HAS Seats =
CALCULATE (
    SUM ( 'Schedule Dynamic Table Report'[Seats] ),
    'Schedule Dynamic Table Report'[Origin Code] = "HOU"
        || 'Schedule Dynamic Table Report'[Origin Code] = "IAH"
)

This will go to the Origin Code and check if the value is HOU or IAH.

The ALL syntax makes your measure ignoring the filters and making the calculations for the full dataset, if you add the ALL and then want to get the HOU with a filter on other column you would always get the total result.

Hope this helps and if you need any additonal explanation I will try to assist.

Regards,

MFelix

[AkhilAshok]

I think you want to use IN. If you specify it as a seperate condition, it acts as an AND. Since one row cannot have both codes, it won't return any results. Another thing is you don't need to explicitly clear the filter context of Origin Code using ALL. If you simply specify the filter as CALCULATE parameter, it automatically clears the filter context on that column.

TEST HAS Seats =
CALCULATE (
    SUM ( 'Schedule Dynamic Table Report'[Seats] ),
    'Schedule Dynamic Table Report'[Origin Code] IN {"HOU", "IAH"}
)

[DAXRichArd]

Ah... I knew that....  No, not really.  Before coming to the forum I read, and read, and researched. I must have glazed over that.

BIG THANK YOU FOR YOUR HELP!  Now I can stop using my stress reduction tool (see attached image).