Forum Discussion
Max per group using filter with DAX
Greetings, i have a table with 3 columns: date, machine and signal (there are lots of machines and signals and date is updated daily)
| Date | Machine | Signal |
| 25/03/2021 | x | A |
| 25/03/2021 | x | B |
| 25/03/2021 | y | A |
| 25/03/2021 | y | B |
| 25/03/2021 | y | C |
| 26/03/2021 | x | C |
| 26/03/2021 | x | A |
| 27/03/2021 | x | A |
| 27/03/2021 | x | B |
| 27/03/2021 | y | C |
What i need is to find out for an specific day how long it has been since i received signals from machines, for example for march 27 it would be:
| A | B | C | |
| x | 0 | 0 | 1 |
| y | 2 | 2 | 0 |
anf for march 26 it would be:
| A | B | C | |
| x | 0 | 1 | 0 |
| y | 1 | 1 | 1 |
I know this is max per group related, but could not came up with the right measure, any help would be appreciated.
Regards
Hi Anonymous
Thanks for your answer, since i have lots of signals i have to use a matrix visual for the columns.
With your help i´ve managed to make a measure that returns exactly what i need, but i have to use two date slicers:
This is the measure i used:
MaxPC =var fecfin = MAX(Fecha[Date])var fecini = CALCULATE(MAX(table[date]),ALLEXCEPT(table,table[equip],table[signal],table[date]))returnDATEDIFF(fecini,fecfin,DAY)
2 Replies
Hi Anonymous
Do you have a Date table with relationship to this table? I have one, the slicer coming from date column from this Date table
test = VAR CurDate = SELECTEDVALUE('Date'[Date]) VAR LDate = COALESCE(MAXX(FILTER(yourTable,yourTable[Date]<=CurDate),yourTable[Date]),CurDate) RETURN DATEDIFF(LDate,CurDate,DAY)Hi Anonymous
Thanks for your answer, since i have lots of signals i have to use a matrix visual for the columns.
With your help i´ve managed to make a measure that returns exactly what i need, but i have to use two date slicers:
This is the measure i used:
MaxPC =var fecfin = MAX(Fecha[Date])var fecini = CALCULATE(MAX(table[date]),ALLEXCEPT(table,table[equip],table[signal],table[date]))returnDATEDIFF(fecini,fecfin,DAY)