Calculation to filter Data

[Berrcikk]

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

[DataInsights · Accepted solution]

@Berrcikk,

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
    vResult

IS Case Closed = IF ( Table1[IS Case Active] = 1, 0, 1 )

[Berrcikk]

Thanks! Works out perfectly