Forum Discussion

nwheelersons's avatar
nwheelersons
Frequent Visitor
8 years ago
Solved

Adding case owner to dax table

Hi, I have created this table (sample) using the following formula so that I could obtain the number of cases opened and closed on the same graph (and it worked)

Cases O/C = 
Selectcolumns(
addcolumns(filter(distinct(SELECTCOLUMNS(union(values('Case data Collector 2'[Date/Time Closed.1].[date]),
VALUES('Case data Collector 2'[Created Date for Reporting.1].[Date])),
"Date", 'Case data Collector 2'[Date/Time Closed.1].[Date])), [Date] <> BLANK()),
"Open", countax(filter(all('Case data Collector 2'),'Case data Collector 2'[Created Date for Reporting.1].[Date]=EARLIER([Date])),[Case Number]),
"Closed", countax(filter(all('Case data Collector 2'), 'Case data Collector 2'[Date/Time Closed.1].[Date]=earlier([Date])),[Case Number])),
 "Date", [Date], "Open", if([Open]<>blank(),[Open], 0), "Closed",if([Closed]<>blank(),[Closed],0))

 

 

The problem I am now having is that I want to also include a filter from the original data source (case owners) I have tried creating a relationship but I have had no luck.

 

so as a last resort if anyone can help it would be much appreciated.

Need Help
  • nwheelersons's avatar
    nwheelersons
    8 years ago

    Managed to solve it, in the end, using summarise and filters using these formulas

     

    Create table

    Test sucessful = SUMMARIZE('Case data Collector 2','Cases O/C'[Date].[Date],'Case data Collector 2'[Case Owner (groups)])

    New Column

    created cases = countax(filter(FILTER(all('Case data Collector 2'),'Case data Collector 2'[Created Date for Reporting.1].[Date]=EARLIER([Date])),'Test sucessful'[Case Owner (groups)] = 'Case data Collector 2'[Case Owner (groups)]),[Case Number])

    New Column: 

    Closed cases = countax(filter(FILTER(all('Case data Collector 2'),'Case data Collector 2'[Date/Time Closed.1].[Date]=EARLIER([Date])),'Test sucessful'[Case Owner (groups)] = 'Case data Collector 2'[Case Owner (groups)]),[Case Number])

    Thanks everyone

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Not sure I am understanding this entirely but it seems like you would have to SUMMARIZE your input table by case owner and include the case owner column in your result table.

    • nwheelersons's avatar
      nwheelersons
      Frequent Visitor
      8 years ago

      basically, i have an original table that has the date a case was created and the date a case was closed. the problem with the graph originally was that I wanted to show on one graph how many cases were opened and closed per month which was not possible with a single x-axis, so I created another table which counted both opened/closed cases to get a unique value for each day so that only one x-axis was needed.

      But now I would like to add a filter from my original dataset to show how many cases were opened and closed by specific case owner.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi nwheelersons,

         

        From current description, it's hard to imaging the table structure. For further analysis, please show us sample data of the original table.

         

        Regards,

        Yuliana Gu

  • nwheelersons's avatar
    nwheelersons
    Frequent Visitor
    8 years ago

    Managed to solve it, in the end, using summarise and filters using these formulas

     

    Create table

    Test sucessful = SUMMARIZE('Case data Collector 2','Cases O/C'[Date].[Date],'Case data Collector 2'[Case Owner (groups)])

    New Column

    created cases = countax(filter(FILTER(all('Case data Collector 2'),'Case data Collector 2'[Created Date for Reporting.1].[Date]=EARLIER([Date])),'Test sucessful'[Case Owner (groups)] = 'Case data Collector 2'[Case Owner (groups)]),[Case Number])

    New Column: 

    Closed cases = countax(filter(FILTER(all('Case data Collector 2'),'Case data Collector 2'[Date/Time Closed.1].[Date]=EARLIER([Date])),'Test sucessful'[Case Owner (groups)] = 'Case data Collector 2'[Case Owner (groups)]),[Case Number])

    Thanks everyone