Forum Discussion
Calculation to filter Data
Hi all,
Currently I'm working on to add additional filters/columns, to provide information how a certain case should be treated.
What I need to do, is to classify a certain case, if it should be open or closed. Let's assume, that today is 24.10.2022. Therefore, all of the records for case A, should be marked as Active, as we have record for todays date. However, for case B, there is no record for today. Therefore all of the cases for case B, should be marked as closed. Please see table below, I think it describes it in the easiest way. Perfectly, it would be to do it in SQL DB, however as I think it's much harder, it is okay to do it in DAX or in M.
Does anyone can help me, how to get the correct syntax? Thanks!
| CASE | DAY | IS Case Active | IS Case closed |
| a | 24.10.2022 | 1 | 0 |
| a | 23.10.2022 | 1 | 0 |
| a | 22.10.2022 | 1 | 0 |
| b | 23.10.2022 | 0 | 1 |
| b | 22.10.2022 | 0 | 1 |
Best regards,
Hubert
Try these calculated columns:
IS Case Active = VAR vCase = Table1[CASE] VAR vToday = TODAY () VAR vTable = FILTER ( Table1, Table1[CASE] = vCase && Table1[DAY] = vToday ) VAR vResult = IF ( ISEMPTY ( vTable ), 0, 1 ) RETURN vResultIS Case Closed = IF ( Table1[IS Case Active] = 1, 0, 1 )
2 Replies
Try these calculated columns:
IS Case Active = VAR vCase = Table1[CASE] VAR vToday = TODAY () VAR vTable = FILTER ( Table1, Table1[CASE] = vCase && Table1[DAY] = vToday ) VAR vResult = IF ( ISEMPTY ( vTable ), 0, 1 ) RETURN vResultIS Case Closed = IF ( Table1[IS Case Active] = 1, 0, 1 )Thanks! Works out perfectly
