Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I’m new to PBI and need some help with a DAX formula.
I have used a column formula to basically display consecutive values (see below). This is great but when I come to apply a filter it doesn’t change dynamically, I think I need to use a measure formula but am unsure of how to apply this.
Basically, I want the report to recalculate the consecutive values when I change the date filter from Min date to Max date.
Column formula: -
Is Consecutive =
CALCULATE(
COUNTROWS(AllocationSums),
FILTER(ALL(AllocationSums),
AllocationSums[Factory]=EARLIER(AllocationSums[Factory])
&&AllocationSums[WeekN]=EARLIER(AllocationSums[WeekN])+0 && AllocationSums[CI UTIL%] >= 90
)
)
Consecutive Times =
SWITCH (
TRUE (),
AllocationSums[Validation] = 0, 0,
CALCULATE (
COUNTROWS ( AllocationSums ),
FILTER (
ALL ( AllocationSums ),
AllocationSums[Validation] = 0
&& AllocationSums[Factory] = EARLIER ( AllocationSums[Factory] )
&& AllocationSums[WeekN] <EARLIER ( AllocationSums[WeekN] )
)
)
= 0, CALCULATE (
SUM ( AllocationSums[Validation] ),
FILTER (
ALL ( AllocationSums ),
AllocationSums[Factory] = EARLIER ( AllocationSums[Factory] )
&& AllocationSums[Week] <= EARLIER ( AllocationSums[Week] )
)
),
CALCULATE (
SUM ( AllocationSums[Validation] ),
FILTER (
ALL ( AllocationSums ),
AllocationSums[Factory] = EARLIER ( AllocationSums[Factory] )
&& AllocationSums[Week]
> CALCULATE (
MAX ( AllocationSums[Week] ),
FILTER (
ALL ( AllocationSums ),
AllocationSums[Validation] = 0
&& AllocationSums[Factory] = EARLIEST ( AllocationSums[Factory] )
&& AllocationSums[Week] < EARLIEST ( AllocationSums[Week] )
)
)
&& AllocationSums[Week] <= EARLIER ( AllocationSums[Week] )
)
)
)
Validation = IF(AllocationSums[Is Consecutive]>=1,1,0)
Hi @roley,
Which field is used to filter? From your formula, the [Is Consecutive] is not related to [Consecutive Times]? Could you please share your sample table in table rather than screenshot, so we can reproduce it rather than type the date manully?
Best Regards,
Angelia
Hi @roley,
Which field is used to filter? You'd better share your .pbix file and list the desired result, so that we can post the solution which is close to your requirement.
Best regards,
Angelia
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |