Measure with multiple if statements and filters

[bizintelguy604]

I'm trying to create a new measure with multiple if statements however I'm getting an error. Any advice?

If ListingStatus = "Inactive" then calculate a particular expression using Calculate(sumx( with filters.

If ListingStatus = "Active" then calculate the same express with slightly different sumx filters.

Here is the measure:
*************************************************
Avg Asking Rate (Weighted) :=
IF (
      Listing[ListingStatus] = "Inactive",
      DIVIDE (
             CALCULATE (
                   SUMX ( 'Listing', Listing[NetAskRate] * Listing[ListingAvailableSF] ),
                   Listing[NetAskRate] > 0,
                   DATESINPERIOD (
                         ListingRemovalDate[ListingRemovalDate_DateOnly],
                         TODAY (),
                          -12,
                          MONTH
                    )
               ),
               CALCULATE (
                     SUM ( [ListingAvailableSF] ),
                     Listing[NetAskRate] > 0,
                     DATESINPERIOD (
                             ListingRemovalDate[ListingRemovalDate_DateOnly],
                             TODAY (),
                              -12,
                           MONTH
                )
         )
),
IF (
        Listing[ListingStatus] = "Active",
        DIVIDE (
              CALCULATE (
                      SUMX ( 'Listing', Listing[NetAskRate] * Listing[ListingAvailableSF] ),
                      Listing[NetAskRate] > 0
               ),
               CALCULATE ( SUM ( [ListingAvailableSF] ), Listing[NetAskRate] > 0 )
          )
     )
)

**************************************************
Here is the Error Warning:

Warning 1 Measure 'Listing'[Avg Asking Rate (Weighted)] : The value for column 'ListingStatus' in table 'Listing' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified. P:\MarketingTabularModel\MarketingTabularModel\Model.bim 0 0 MarketingTabularModel

[amitchandak]

@bizintelguy604 , Try like

Avg Asking Rate (Weighted) :=
IF (
      max(Listing[ListingStatus]) = "Inactive",
      DIVIDE (
             CALCULATE (
                   SUMX ( 'Listing', Listing[NetAskRate] * Listing[ListingAvailableSF] ),
                   Listing[NetAskRate] > 0,
                   DATESINPERIOD (
                         ListingRemovalDate[ListingRemovalDate_DateOnly],
                         TODAY (),
                          -12,
                          MONTH
                    )
               ),
               CALCULATE (
                     SUM ( [ListingAvailableSF] ),
                     Listing[NetAskRate] > 0,
                     DATESINPERIOD (
                             ListingRemovalDate[ListingRemovalDate_DateOnly],
                             TODAY (),
                              -12,
                           MONTH
                )
         )
),
IF (
        max(Listing[ListingStatus]) = "Active",
        DIVIDE (
              CALCULATE (
                      SUMX ( 'Listing', Listing[NetAskRate] * Listing[ListingAvailableSF] ),
                      Listing[NetAskRate] > 0
               ),
               CALCULATE ( SUM ( [ListingAvailableSF] ), Listing[NetAskRate] > 0 )
          )
     )
)

[ImkeF]

Hi @amitchandak ,

wondering if you spotted other flaws in the measure or did you just write out the full measure code with the changes I suggested?

[amitchandak]

@ImkeF , My bad miss. Thanks for pointing. 

[ImkeF · Accepted solution]

Hi @bizintelguy604 ,

the first argument in your If-statement is not valid:

Avg Asking Rate (Weighted) :=
IF (
      Listing[ListingStatus] = "Inactive",

this is referencing a whole column, but you need a single value (scalar) instead.
This could be achieved  by an aggregtion function, like MIN or MAX:

Avg Asking Rate (Weighted) :=
IF (
      MAX ( Listing[ListingStatus] ) = "Inactive",