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

      • SabineOussi's avatar
        SabineOussi
        Icon for Skilled Sharer rankSkilled Sharer
        4 years ago

        Hello v-easonf-msft 

        Going back to this after a long vacation.

        The solution provided returns correct results, however it is way slower than LowSalesReasons_1 (which was already slow and that was the main issue) and eventually prompts a not enough memory error even on PBI Service.

        Error Message:
        There's not enough memory to complete this operation. Please try again later when there may be more memory available.


        Any idea?

        Thanks.
        Sabine O.