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.

lbendlin's avatar
lbendlin
Super User
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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? 

 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.


    • 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
        Community 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.