Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 a...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Sure, edited my initial message to include a table of sample data and expected results

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    That completed it, thank you both so much in getting me on the right track!  I really appreciate y'all helping me out.