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 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
Solved! Go to Solution.
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 )
Proud to be a Super User!
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 )
Proud to be a Super User!
Thanks! Works out perfectly
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |