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.
Hi guys,
in my table there is [date], [ID], [meter reader] and [status] - when the meter reader stopped counting status switch from "ok" to "stopped". So here is an example:
Date | ID | meter reader | status |
01.01.2020 | 3 | 200 | ok |
02.01.2020 | 3 | 230 | ok |
03.01.2020 | 3 | 230 | stopped |
04.01.2020 | 3 | 230 | stopped |
05.01.2020 | 3 | 240 | ok |
06.01.2020 | 3 | 240 | stopped |
07.01.2020 | 3 | 240 | stopped |
08.01.2020 | 3 | 240 | stopped |
Now I want to create a DAX measure, which calculates the number of days the meter reader stopped! In this case the measure should only consider the last 3 days because the day before status displayed "ok". So for how many days the meter reader stopped counting since last status was ok?
Any help is much appreciated!
Cheers!!
Solved! Go to Solution.
Hi @IEPMost ,
We can create a calculate column to meet your requirement.
Date count =
var _max_ok_date = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[status]="ok"))
return
IF(
'Table'[Date]>_max_ok_date,1,0)
Then we can put the column in a card visual.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @IEPMost ,
Do you want to show a table visual like this? Just ID and Date count?
If yes, you can put the ID and Date count into Values. And you don’t need to create a measure.
If it doesn’t meet your requirement, could you please show the exact expected result?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @IEPMost ,
We can create a calculate column to meet your requirement.
Date count =
var _max_ok_date = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[status]="ok"))
return
IF(
'Table'[Date]>_max_ok_date,1,0)
Then we can put the column in a card visual.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @v-zhenbw-msft ,
thank you so much for your help! It´s not exactly what I´m looking for because I need the datecount for every ID seperately. So in your example I need the count of 3 for the ID 3 and a count of 4 for ID 4.
I wanted to create a table visual in which you can see the ID and for how many days the meter reader stopped counting (so the status column doesn´t need to be in my visual, only for how many days reader stopped counting). That´s why I wanted to create a DAX Measure. Is this the right way to go?
Thanks again and I hope you know what I mean.
Cheers
Try something like:
Days =
VAR _lastOK = MAXX(FILTER(Table, Table[Reader Status] = "OK"), Table[Date])
RETURN
COUNTROWS(FILTER(Table, Table[Date] > _lastOK))
Depending on how you want to use it, you may need to add in some additional filters like ALL or ALLSELECTED
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @AllisonKennedy ,
thanks a lot for your help! Unfortunately this DAX Code doesn´t work out for me. It seems that my table still counts meter readers which stopped in the past but in the meantime switched to ok again. I don´t know why because your code makes absolute sense to me. Any ideas?
I forgot to mention that in my example data are more than one ID, so I have the meter reader for many ID´s. How should I consider that? The measure must count the status "stopped" for every single ID.
Thank you a lot!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |