Creating a Chart with x axis to be dynamic

[PBI5851]

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.

[tex628 · Accepted solution]

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

[PBI5851]

Thank you @tex628 . Wil try it out. Can you send me your pbix file please.

[tex628]

Here you go:
https://drive.google.com/file/d/1U7AAwM-2ACx1sZvUbUwvcBam0dFT4H-r/view?usp=sharing