Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |