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.
I am trying to re-create "New case" in power bi that would identify as Yes if its is the first new entry of a uniqe case ID.
the Column would be as below
Uniqe Case ID | Date Logged | Completed Date | Report Month | New Cases |
ID1 | 2020-01-21 | - | Jan | Yes |
ID1 | 2020-01-21 | - | Feb | No |
ID1 | 2020-01-21 | 2020-03-22 | March | No |
ID2 | 2020-02-01 | - | Feb | Yes |
ID2 | 2020-02-01 | 2020-03-02 | March | No |
ID3 | 2020-03-01 | 2020-03-12 | March | Yes |
ID4 | 2020-01-01 | 2020-01-09 | Jan | Yes |
Solved! Go to Solution.
hi @Anonymous
For your case, you need add a Report Month No column in the table.
for example, you could use this formula to get it.
Report Month No = SWITCH('Table'[Report Month],
"Jan",1,
"Feb",2,
"March",3
)
Then use this formula to get the expected output column:
Result New cases =
IF( CALCULATE(MIN('Table'[Report Month No]),FILTER('Table','Table'[Uniqe Case ID]=EARLIER('Table'[Uniqe Case ID])))='Table'[Report Month No],"Yes","No")
Result:
and here is sample pbix file, please try it
Regards,
Lin
hi @Anonymous
For your case, you need add a Report Month No column in the table.
for example, you could use this formula to get it.
Report Month No = SWITCH('Table'[Report Month],
"Jan",1,
"Feb",2,
"March",3
)
Then use this formula to get the expected output column:
Result New cases =
IF( CALCULATE(MIN('Table'[Report Month No]),FILTER('Table','Table'[Uniqe Case ID]=EARLIER('Table'[Uniqe Case ID])))='Table'[Report Month No],"Yes","No")
Result:
and here is sample pbix file, please try it
Regards,
Lin
Hi @Anonymous ,
Your requirement is not complete as you haven't mentioned how the first new entry against a Unique Case ID is considered.
Is it based on Report Month or somehting using Date Logged and Completed Date?
Thanks,
Pragati
What i am trying to capture in each report month is when the unique ID was first loggged based on date logged.
So if it was logged in Jan, then in report month it should identify it as a new case. where as if it was logged in in March, then the report month for March should identify it as a new case.
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 |
---|---|
98 | |
97 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |