Forum Discussion
Creating a Chart with x axis to be dynamic
Hello,
I am trying a develop a graph using the below data
| OrderNo | OrdDate | CallType | CallDate | CallID |
| A123 | 12/20/2022 | Initial | 1/2/2023 | C1234 |
| A123 | 12/20/2022 | Initial | 1/2/2023 | C1235 |
| A123 | 12/20/2022 | Service | 1/5/2023 | C1236 |
| A123 | 12/20/2022 | Service | 1/7/2023 | C1237 |
| A123 | 12/20/2022 | Service | 1/8/2023 | C1238 |
| A123 | 12/20/2022 | Shipping | 1/8/2023 | C1239 |
| A123 | 12/20/2022 | Shipping | 1/8/2023 | C1240 |
| A123 | 12/20/2022 | Service | 1/9/2023 | C1241 |
| A123 | 12/20/2022 | Service | 1/10/2023 | C1242 |
| A123 | 12/20/2022 | Service | 1/11/2023 | C1243 |
| A123 | 12/20/2022 | Service | 1/12/2023 | C1244 |
| B123 | 11/15/2022 | Initial | 1/3/2023 | C1245 |
| C123 | 11/20/2022 | Initial | 1/5/2023 | C1246 |
| D123 | 11/25/2022 | Initial | 1/5/2023 | C1247 |
| D123 | 11/25/2022 | Service | 1/10/2023 | C1248 |
| D123 | 11/25/2022 | Shipping | 1/10/2023 | C1249 |
| E123 | 1/2/2023 | Initial | 1/2/2023 | C1250 |
| E123 | 1/2/2023 | Shipping | 1/5/2023 | C1251 |
| E123 | 1/2/2023 | Shipping | 1/5/2023 | C1252 |
The requirement is to have a calldate and orddate slicer but plot in 3 unique graphs the below.
- An Initial call count graph
- A Service call count graph
- A shipping call count graph
the graph should be the distinct count of Orders who have had 0, 1, 2, 3, 4, 5 Initial call, where 0,1,2,3,4,5 is on the x axis
For a Shipping call count graph:
With a OrdDate Slicer from 11/1/22 - 1/31/22 AND with a call date slicer as 12/1/22 - 12/20/22
the first column for 0 (on x axis) should be 5 (this is because none of the 5 orders had calls within the secondary date range)
With a OrdDate Slicer from 11/1/22 - 1/31/22 AND with a call date slicer as 1/1/23 - 1/20/23
the first column for 0 (on x axis) should be 2 (this is because B123 and C123 had 0 calls within the secondary date range)
the second column for 1 (on x axis) should be 3 (because A123, D123, E123 each had atleast 1 shipping call within the secondary date range)
With a OrdDate Slicer from 11/1/22 - 1/31/22 AND with a call date slicer as 1/1/23 - 1/5/23
the first column for 0 (on x axis) should be 4 (this is because A123, B123, C123, D123 had 0 calls within the secondary date range)
the second column for 1 (on x axis) should be 1 (because A123, D123, E123 each had atleast 1 shipping call within the secondary date range)
The same concept for Initial and Service calls. I did try to use a summarize table solution, but the slicer is throwing me off. When i select the date, the members with no calls dont show (i.e column with 0 calls). And if i use the EXCEPT , then the filter is not applied at all.
Any recommendations on how to resolve this please.
Hi PBI5851 ,
I've tried to recreate what i believe is your end-goal.
How does this match your intended output?
It's done with the following measures:Shipping Call Count = VAR totalOrders = CALCULATE( DISTINCTCOUNT( Orders[OrderNo] ) , REMOVEFILTERS( Orders[CallDate] )) VAR WithCalls = COUNTROWS( FILTER ( SUMMARIZE( Orders , Orders[OrderNo] , Orders[CallType] , "Value" , COUNTROWS( Orders ) ) , [Value] >= 1 && Orders[CallType] = "Shipping") ) Return SWITCH( SELECTEDVALUE( 'Count'[Count] ) , "0" , totalOrders - WithCalls , "1" , WithCalls )Service Call Count = VAR totalOrders = CALCULATE( DISTINCTCOUNT( Orders[OrderNo] ) , REMOVEFILTERS( Orders[CallDate] )) VAR WithCalls = COUNTROWS( FILTER ( SUMMARIZE( Orders , Orders[OrderNo] , Orders[CallType] , "Value" , COUNTROWS( Orders ) ) , [Value] >= 1 && Orders[CallType] = "Service") ) Return SWITCH( SELECTEDVALUE( 'Count'[Count] ) , "0" , totalOrders - WithCalls , "1" , WithCalls )Initial Call Count = VAR totalOrders = CALCULATE( DISTINCTCOUNT( Orders[OrderNo] ) , REMOVEFILTERS( Orders[CallDate] )) VAR WithCalls = COUNTROWS( FILTER ( SUMMARIZE( Orders , Orders[OrderNo] , Orders[CallType] , "Value" , COUNTROWS( Orders ) ) , [Value] >= 1 && Orders[CallType] = "Initial") ) Return SWITCH( SELECTEDVALUE( 'Count'[Count] ) , "0" , totalOrders - WithCalls , "1" , WithCalls )
Plus a small static table to populate the x-axis:
/ J
3 Replies
Hi PBI5851 ,
I've tried to recreate what i believe is your end-goal.
How does this match your intended output?
It's done with the following measures:Shipping Call Count = VAR totalOrders = CALCULATE( DISTINCTCOUNT( Orders[OrderNo] ) , REMOVEFILTERS( Orders[CallDate] )) VAR WithCalls = COUNTROWS( FILTER ( SUMMARIZE( Orders , Orders[OrderNo] , Orders[CallType] , "Value" , COUNTROWS( Orders ) ) , [Value] >= 1 && Orders[CallType] = "Shipping") ) Return SWITCH( SELECTEDVALUE( 'Count'[Count] ) , "0" , totalOrders - WithCalls , "1" , WithCalls )Service Call Count = VAR totalOrders = CALCULATE( DISTINCTCOUNT( Orders[OrderNo] ) , REMOVEFILTERS( Orders[CallDate] )) VAR WithCalls = COUNTROWS( FILTER ( SUMMARIZE( Orders , Orders[OrderNo] , Orders[CallType] , "Value" , COUNTROWS( Orders ) ) , [Value] >= 1 && Orders[CallType] = "Service") ) Return SWITCH( SELECTEDVALUE( 'Count'[Count] ) , "0" , totalOrders - WithCalls , "1" , WithCalls )Initial Call Count = VAR totalOrders = CALCULATE( DISTINCTCOUNT( Orders[OrderNo] ) , REMOVEFILTERS( Orders[CallDate] )) VAR WithCalls = COUNTROWS( FILTER ( SUMMARIZE( Orders , Orders[OrderNo] , Orders[CallType] , "Value" , COUNTROWS( Orders ) ) , [Value] >= 1 && Orders[CallType] = "Initial") ) Return SWITCH( SELECTEDVALUE( 'Count'[Count] ) , "0" , totalOrders - WithCalls , "1" , WithCalls )
Plus a small static table to populate the x-axis:
/ J
