- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# CALCULATE produces a blank result

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2018 11:34 AM - edited 11-14-2018 11:38 AM

**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

Solved! Go to Solution.

Accepted Solutions

## Re: CALCULATE produces a blank result

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2018 11:54 AM

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

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

All Replies

## Re: CALCULATE produces a blank result

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2018 11:49 AM - edited 11-14-2018 11:52 AM

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"} )

## Re: CALCULATE produces a blank result

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2018 11:54 AM

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

**Did I answer your question? Mark my post as a solution!**

Proud to be a Datanaut!

## Re: CALCULATE produces a blank result

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2018 12:12 PM

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).