Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 ....
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.
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |