Forum Discussion

SabineOussi's avatar
SabineOussi
Skilled Sharer
4 years ago

DAX - using variable in measure filter

Hello Community,

 

I'm trying to pass a variable to a measure filter and it's giving me wrong results when using VAR and correct but very slow performance when using the measure itself.

 

Here are the two measures and their difference:

 

LowSalesReason_1 correct results but very slow performance

 

LowSalesReasons_1 = 
VAR target = [%_Target]

VAR t2 =
    COUNTROWS ( FILTER ( ProductsDetails, [ProductCategory] = "Tier 2" ) ) + 0
VAR t3 =
    COUNTROWS ( FILTER ( ProductsDetails, [ProductCategory] = "Tier 3" ) ) + 0
VAR t4 =
    COUNTROWS ( FILTER ( ProductsDetails, [ProductCategory] = "Tier 4" ) ) + 0

return
SWITCH(
    TRUE(),
target > 0.45, "Majority Sold", 
t2 > (t3 + t4), "Low Sales Rates",
t4 > (t2 + t3), "High Inquiries", 
t3 > t2, "High Inquiries", 
"Low Sales Rates & High Inquiries")

 

 

LowSalesReason_2 incorrect results but quite fast

 

LowSalesReasons_2 = 
VAR target = [%_Target]
VAR prodcat = [ProductCategory]

VAR t2 =
    COUNTROWS ( FILTER ( ProductsDetails, prodcat = "Tier 2" ) ) + 0
VAR t3 =
    COUNTROWS ( FILTER ( ProductsDetails, prodcat = "Tier 3" ) ) + 0
VAR t4 =
    COUNTROWS ( FILTER ( ProductsDetails, prodcat = "Tier 4" ) ) + 0

return
SWITCH(
    TRUE(),
target > 0.45, "Majority Sold", 
t2 > (t3 + t4), "Low Sales Rates",
t4 > (t2 + t3), "High Inquiries", 
t3 > t2, "High Inquiries", 
"Low Sales Rates & High Inquiries")

 

 

 

Any idea what's going on?

 

Thanks,

Sabine O.

Need Help
Tips and Tricks

6 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    Hi SabineOussi 

    LowSalesReasons_2  is incorect because VAR prodcat only computes once and not for each row.

     

    To improve speed try replace FILTER with CALCULATE and don't add the+ 0

     

    Consider this solution and click the thumbs up button  ....

     

    LowSalesReasons_3 =
    VAR target = [%_Target]
    VAR t2 =
    CALCULATE( COUNTROWS(ProductsDetails), ProductsDetails[ProductCategory] = "Tier 2")
    VAR t3 =
    CALCULATE( COUNTROWS(ProductsDetails), ProductsDetails[ProductCategory] = "Tier 3")
    VAR t4 =
    CALCULATE( COUNTROWS(ProductsDetails), ProductsDetails[ProductCategory] = "Tier 4")

    RETURN

    SWITCH(
    TRUE(),
    target > 0.45, "Majority Sold",
    t2 > (t3 + t4), "Low Sales Rates",
    t4 > (t2 + t3), "High Inquiries",
    t3 > t2, "High Inquiries",
    "Low Sales Rates & High Inquiries")
    • SabineOussi's avatar
      SabineOussi
      Skilled Sharer
      4 years ago

      Thanks speedramps for your reply.

       

      The issue is ProductCategory being referred to by prodcat is a measure and not a column, your solution gives the below error:

      A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

      • speedramps's avatar
        speedramps
        Super User
        4 years ago

        Hi SabineOussi

         

        Sorry to hear that. I think perhaps you have not copied and pasted my measure.

        It sound like you have created a prodcat measure and are trying to use that in the CALCULATE expression .... you cant' do that ... and will get a syntax error.

        This measure however should be ok ... depending on your data model structure, which you didnt supply.
         

        LowSalesReasons_3 =
        VAR target = [%_Target]
        VAR t2 =
        CALCULATE( COUNTROWS(ProductsDetails), ProductsDetails[ProductCategory] = "Tier 2")
        VAR t3 =
        CALCULATE( COUNTROWS(ProductsDetails), ProductsDetails[ProductCategory] = "Tier 3")
        VAR t4 =
        CALCULATE( COUNTROWS(ProductsDetails), ProductsDetails[ProductCategory] = "Tier 4")

         

        RETURN

         

        SWITCH(
        TRUE(),
        target > 0.45, "Majority Sold",
        t2 > (t3 + t4), "Low Sales Rates",
        t4 > (t2 + t3), "High Inquiries",
        t3 > t2, "High Inquiries",
        "Low Sales Rates & High Inquiries")