Adding case owner to dax table

[nwheelersons]

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.

[nwheelersons · Accepted solution]

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

[Greg_Deckler]

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]

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]

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