Forum Discussion

SabineOussi's avatar
SabineOussi
Icon for Skilled Sharer rankSkilled 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 ar...
Need Help
Tips and Tricks
speedramps's avatar
speedramps
Icon for Super User rankSuper 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
Icon for Skilled Sharer rankSkilled 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
    Icon for Super User rankSuper 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")
    • SabineOussi's avatar
      SabineOussi
      Icon for Skilled Sharer rankSkilled Sharer
      4 years ago

      Thank you speedramps for your prompt response.

       

      Here is a screenshot of the error with the provided measure LowSalesReasons_3

       

       

      Actually, this is my initial posting with a link to an uploaded sample.

      https://community.powerbi.com/t5/Desktop/Measure-Flagging-Performance/m-p/2129167

      where I eventually replaced LowSalesReasons by the mentioned LowSalesReasons_1 or LowSalesReasons_2.

      AggregateLowSalesReasons follows the same clauclation reasoning and will be replaced by the same solution provided here.
       
      Thank you,
      Sabine O.
      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, SabineOussi 

        Has your problem been resolved? You can try to change the formula in your screenshot as follows.

        owSalesReasons_3 =
VAR target = [%_Target]
VAR t2 =
    CALCULATE (
        COUNTROWS ( ProductsDetails ),
        FILTER ( ProductsDetails, [ProductCategory] = "Tier 2" )
    )
VAR t3 =
    CALCULATE (
        COUNTROWS ( ProductsDetails ),
        FILTER ( ProductsDetails, [ProductCategory] = "Tier 3" )
    )
VAR t4 =
    CALCULATE (
        COUNTROWS ( ProductsDetails ),
        FILTER ( 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"
    )

        Best Regards,
        Community Support Team _ Eason