Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I'm trying to make a measure that: counts how many times a Product has a Value below 1,0. The count has to take into account that it is consecutive, but also counts down from the most recent week.
In my example below:
- Product A should get a measure count of 0 (because the most recent week, W3, has a value of above 1,0)
- Product B should get a measure count of 2 (because the most recent two weeks are below 1,0).
Product | Week | Value |
A | W1 | 0,9 |
A | W2 | 0,9 |
A | W3 | 1,1 |
B | W1 | 1,0 |
B | W2 | 0,9 |
B | W3 | 0,9 |
I hope someone has an idea on how to make this work. I could not find anything that tackled the 'most recent week' part of the question unfortunately.
Thanks in advance for taking the time and effort 🙂
Solved! Go to Solution.
hi @FVP
try like:
Count =
VAR _maxweek = MAX(TableName[Week2])
VAR _week =
MAXX(
FILTER(
TableName,
TableName[Value]>=1||TableName[Value]=BLANK()
),
TableName[Week2]
)
RETURN
_maxweek - _week
hi @FVP
try to
1) add a calculated column with:
Week2 = SUBSTITUTE([Week],"W", "")
(It is better if you can do it in Power Query Editor)
2) write a measure like:
Count =
VAR _maxweek = MAX(TableName[Week2])
VAR _week =
MAXX(
FILTER(
TableName,
TableName[Value]>=1
),
TableName[Week2]
)
RETURN
_maxweek - _week
it worked like:
Hi FreemanZ,
Your solution is working, but my dataset has made it a bit more complicated.
I also have Products that dont have any values, so they are blank. This messes up the count measure you have written. The measure now also counts the blanks.
What would I need to alter in the measure to keep it from counting blanks as <1,0?
Thanks for your help! Happy with the current result either way 🙂
Hi FreemanZ,
Unfortunately, the ones that have only blanks are the one with get a count of 52 (52weeks).
It works great for the other ones though, as you can see from my screenshot below.
So I need something like a COUNTA that ignore blank, but Im not sure how I would fix this in this measure. Would you know this perhaps?
Hi FreemanZ,
Thanks for taking the time to help me.
You measure works good for capturing 0 for Product A and 2 for Product B.
Two new examples added:
Product C in the example below would get a 3, but should get a 0 instead (edit: or maybe preferably, blank. But 0 is fine too).
Product D works good. It records a 0
Product | Week | Value |
A | W1 | 0,9 |
A | W2 | 0,9 |
A | W3 | 1,1 |
B | W1 | 1,0 |
B | W2 | 0,9 |
B | W3 | 0,9 |
C | W1 | |
C | W2 | |
C | W3 | |
D | W1 | 0,9 |
D | W2 | |
D | W3 | 1,1 |
hi @FVP
try like:
Count =
VAR _maxweek = MAX(TableName[Week2])
VAR _week =
MAXX(
FILTER(
TableName,
TableName[Value]>=1||TableName[Value]=BLANK()
),
TableName[Week2]
)
RETURN
_maxweek - _week
Hi @FreemanZ - I need to do something similar and was hoping to use your solution. .. but my count comes up as zero's. Any ideas would be appreciated. Thanks, John
Hi,
I know I've accepted the solution, but I was hoping you might know a quick fix for the following.
Whenever I add a slicer, to filter on only a specific number of weeks, measure cant cope with it.
When I set a slicer for only W2 and W3, for example.
Is there a quick fix in the measure for this? I've tried multiple things but couldnt work it out im afraid. It's driving me nuts!
Product | Week | Value |
A | W1 | 0,9 |
A | W2 | 0,9 |
A | W3 | 1,1 |
B | W1 | 1,0 |
B | W2 | 0,9 |
B | W3 | 0,9 |
C | W1 | |
C | W2 | |
C | W3 | |
D | W1 | 0,9 |
D | W2 | |
D | W3 | 1,1 |
Hi FreemanZ,
That did the trick! It returns the right value in all my testrows 🙂
Thank you for your patience and time!
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |