Creating a Measure To Count the Number of Times a Condition Occurred at some Future Date

[Anonymous]

Hello all,

I am looking for a measure that counts the number of times that a certain condition happens. The condition is such that a status changes from 0 (at some date) and then to 1 at a later date in a fact table.

Below is an example of the fact table:

Here is the desired output:

Here is the link to the .pbix file

I am assuming that COUNTX has to be used here but I am not sure how to formulate the expression so that the measures only searches the dates in which the condition has not already been met. Any tips are greatly appreciated! Thanks!

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Please kindly let me know if you'd like to get below results:

Please filter the measure is not blank:

Count = var a = CALCULATE(MAX(Transactions[Date]),FILTER(ALL(Transactions),[Date]<MAX(Transactions[Date])&&[Status]="0"),VALUES(Transactions[Material]))
Return
COUNTX(Transactions,a)

[Anonymous · Accepted solution]

I was able to come up with the solution using EARLIER( ). Here is the DAX I used:

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Please kindly let me know if you'd like to get below results:

Please filter the measure is not blank:

Count = var a = CALCULATE(MAX(Transactions[Date]),FILTER(ALL(Transactions),[Date]<MAX(Transactions[Date])&&[Status]="0"),VALUES(Transactions[Material]))
Return
COUNTX(Transactions,a)

[smpa01]

@Anonymous  can you please try this

[Anonymous]

Hi, that solution did not work, my apologies for providing the sample table that may have been unclear. I have included a screenshot of the measure with a different table. Please note that the correct output should show a count of 2 for A and 1 for C 

[amitchandak]

Now I am confused. What exactly you want. If you want sum 0, that B should also be. Last time it seems you want to sum when you one the value as 0

Sum of 0

sumx(table,if(table[status]=0,1,0))

[Anonymous]

To clarify the count is the number of incidences in which some material switches from being status of 0 to status of 1. So in the example above A goes from 0 to 1 a total of 2 times and C goes from 0 to 1 a total of 1 time., B never has a status of 0 then 1 (in the future), so that count is 0.

[amitchandak]

as column you can filter like
if(countx(table[status])-sumx(table[status])>1,1,0)

use this as filter

As measure to be displayed with material
calculate(countrows(table),values([material]),filter(table,(countx(table[status])-sumx(table[status]))>0))