PowerPivot - filter the result of a measure where data does not originate in data set.

[DAXRichArd]

Hello,

My software platform is Excel 2016 in Microsoft Professional Plus 2016.

I am using PowerPivot.

CURRENT STATE

• My data set is passenger airline data with several dimensions (time series, airline name, direction (arrival, connection, or depature).
• I have created a time series forecast using a multiplier of 1%.

OUTPUT

• I display current passenger data in a time series, else if current data is not available forecast last years data times 1%. I wrote a DAX measure for this.
• My slicers in the Pivot Table are working.
• I display the time series horizontally and break out the passengers by 2 of the 3 dimentions (departure and connection). These are enplanements, passengers leaving the airport on a flight.

ISSUE

• I have an airline that recently started service (Ethiopian Airlines, December 2019). They do not have historical data.
• I have created a measure that projects the number of passengers they will have.
  • 270 seats x 80% occupancy = 216 passengers.
    • Of that, 90% should be Departure (216*0.9 = 194.4) and 10% should be Connection (216*0.1 = 21.6)

PROBLEM / DESIRED OUTCOME

• I want this measure to calculate and display based on the direction slicer I choose in the pivot table (i.e. Departure and Connection).

My know-how is limited.

I've tried using IF in CALCULATE, but I get a logic error message.

I tried creating a helper column converting Departure to number 1 and Connection to number 2, then using the IF within CALCULATE.

Tried using VALUES in the measure.

I'm just hacking away but I can't figure out if it is possible.

Is what I want to do possible? I can provide the file. Your help is greatly appriciated. See also attached pic. Have a great day!

[sturlaws]

Hi @DAXRichArd ,

I don't quite understand what you are trying to do. A sample file/report would be most helpful.

Cheers,
Sturla

[DAXRichArd]

Hello @sturlaws ,

I sent you a private msg with a link to the file.

@DAXRichArd 

[sturlaws · Accepted solution]

Thanks for the file, it made everything a bit clearer.

First of all, you need to create a new table with the values Arrival, Departure and Connection, you could call it dim_direction:

Link dim_directon to your Pax-table on Pax[Direction]:

 Then replace the existing Direction-slicer with a new slicer from dim_direction[Direction]. This new table can be created in the Excel-file and added to the model.

Then you need to rewrite your measure to this:

Ethiopian 80% LF :=
VAR _DaysInSeries = [ET DaysInSeries]
VAR _ET = 270 * 0,8
RETURN
    IF (
        HASONEVALUE ( dim_direction[Direction] );
        SWITCH (
            VALUES ( dim_direction[Direction] );
            "Departure"; _ET * _DaysInSeries * 0,9;
            "Connection"; _ET * _DaysInSeries * 0,1;
            "Arrival"; BLANK ()
        );
        _DaysInSeries * _ET
    )

The underscores in the begining of the variable names is just there to make it easier to reckognize the variables in the code. It is also considered best practice not to include table name when referencing a measure. E.g. instead of 'Calendar'[ET DaysInSeries], write just [ET DaysInSeries]. Table names should only be included when referencing a column.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[DAXRichArd]

Thank you! It works. Now I have to reflect on it to understand why it works. I gonna see if I can make is work for my WN 3n6% FY21 measure.

Big Big thank you and I wish I was as smart as you in DAX!