DAX - using variable in measure filter

[SabineOussi]

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.

[speedramps]

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]

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]

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]

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]

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]

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.