Forum Discussion
Creating Table and Adding Column From Existing Table
Hi, I'm inserting a new table and got part of it working properly but can't figure out how to get through one more step. I've used the below DAX to summarize our cases sitting in an open status on any given date:
Open Cases =
ADDCOLUMNS (
CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])),
"Count", CALCULATE (
DISTINCTCOUNT ( 'Case History'[CaseNumber] ),
FILTER (
'Case History',
'Case History'[CreatedDate] <= [Date]
&& (
'Case History'[ClosedDate] > [Date]
|| ISBLANK ( 'Case History'[ClosedDate] )
)
)
)
)
And this works as expected giving the results below:
However I'd like to get the Count split for each date by another field 'Case History'[Origin] from the same existing Case History table. I've tried working with SUMMARIZE and some other options but keep getting errors so not sure what I'm doing wrong. The ideal output I'd like to have is below, if anyone could please help point me in the right direction?
EDIT: Sample table data:
| CreatedDate | ClosedDate | CaseNumber | Origin |
| 1/3/2021 | 1/3/2021 | 1 | Customer Initiated |
| 12/20/2020 | 1/3/2021 | 2 | Customer Initiated |
| 1/2/2021 | 1/4/2021 | 3 | Customer Initiated |
| 12/2/2020 | 4 | Internal | |
| 12/31/2020 | 1/2/2021 | 5 | Internal |
| 12/1/2020 | 12/9/2020 | 6 | Internal |
| 1/2/2021 | 1/3/2021 | 7 | Customer Initiated |
| 1/1/2021 | 8 | Customer Initiated | |
| 1/6/2021 | 9 | Customer Initiated | |
| 1/1/2021 | 1/8/2021 | 10 | Internal |
Using this sample, I'd like to see results of the 'open case' table as something like this:
| Snapshot Date | Open Count | Origin |
| 1/1/2021 | 3 | Internal |
| 1/1/2021 | 2 | Customer Initiated |
| 1/2/2021 | 2 | Internal |
| 1/2/2021 | 4 | Customer Initiated |
| 1/3/2021 | 2 | Internal |
| 1/3/2021 | 2 | Customer Initiated |
Hi Anonymous ，
Please try the following formula:
Open Cases = ADDCOLUMNS ( CROSSJOIN( CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])), DISTINCT('Case History'[Origin]) ), "Count", CALCULATE ( DISTINCTCOUNT ( 'Case History'[CaseNumber] ), FILTER ( 'Case History', 'Case History'[CreatedDate] <= [Date] && 'Case History'[Origin] = EARLIER('Case History'[Origin]) && ( 'Case History'[ClosedDate] > [Date] || ISBLANK ( 'Case History'[ClosedDate] ) ) ) ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Instead of
ADDCOLUMNS ( CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])),You would say something like
ADDCOLUMNS ( CROSSJOIN(CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])),VALUES('Case History'[Origin])),and then apply the Origin filter as needed.
Cool thank you, that did work to add the Origin column in, separating into different rows for the different Origin values, I just have one more question. The Counts are showing the monthly totals as the same for each Origin row rather than subdividing the totals, I've tried to do some things with the filter but no luck; do you know how I'd resolve that?
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.
