Avoid aggregation for scatter and box & whisker chart

[HenningB]

Hi Community,

I have read multiple posts in the community (and outside) regarding unintended aggregation of data based on unsatisfying level of granularity, but none of the suggestion really doing the trick for me (or I am simply unable to follow properly). Either way, let me show you, what I got and what I'd like to achieve.

Data Source

GP Date Only	license_plate	gatepass_nr	gp_to_driver	gate_in	gate_out	Gate Waiting Time [min]	Yard Layover [min]	Weekday [Mon=1]
06.05.2021	19BGX9	202105040248	06.05.2021 07:53	06.05.2021 08:00	06.05.2021 08:34	7	34	4
06.05.2021	BCAA3060	202104270114	06.05.2021 07:37	06.05.2021 07:43	06.05.2021 08:34	6	51	4
06.05.2021	SCZ72354	202104300147	06.05.2021 07:26	06.05.2021 07:42	06.05.2021 09:58	16	16	4
06.05.2021	FR464BF	202104230176	06.05.2021 07:10	06.05.2021 07:28	06.05.2021 07:50	17	22	4
06.05.2021	43BPP3	202104300145	06.05.2021 07:03	06.05.2021 07:09	06.05.2021 08:02	6	52	4
06.05.2021	49BNJ7	202104300211	06.05.2021 07:01	06.05.2021 07:05	06.05.2021 07:40	4	34	4
06.05.2021	LIP417	202105060016	06.05.2021 06:57	06.05.2021 07:02	06.05.2021 07:29	5	27	4
06.05.2021	1THL047	202104270038	06.05.2021 06:55	06.05.2021 06:58	06.05.2021 07:33	3	34	4
06.05.2021	KHA656	202105060012	06.05.2021 06:46	06.05.2021 06:53	06.05.2021 07:29	6	36	4
06.05.2021	FZA40241	202105060011	06.05.2021 06:46	06.05.2021 06:54	06.05.2021 07:22	8	28	4

That sample is out of a 400.000+ record set. I now want to create two charts:

1) Scatter plot with all the individual data points on the Y-axis with the date on the X-axis (okay, to only use a sub-set)

2) Box & Whisker chart summarizing all data per weekday across the whole data set

1) I wasn't able to pull off at all. 

2) Requires me to use an aggregated form of the waiting time, which is obviously completely bullocks if I want to see the distribution of the waiting time.

Any help is highly appreciated. Can I fix this somehow with a "fake" DAX measure? 

[Greg_Deckler · Accepted solution]

@HenningB Have you tried using the Legend field well? I attached an example under the signature line. 

[HenningB]

@Greg_Deckler, this works indeed. Need to make sure to limit the amount of data, though. Otherwise Power BI crashes.

[Greg_Deckler]

@HenningB What are the other fields you are using in your scatter chart?

[HenningB]

Hi @Greg_Deckler,

thanks for taking time to look at my challenge.

My actual ambition was to capture the combination of waiting time and at which hour the truck arrived at the gate - similar to this request: Scatter with date on X and time on Y - Microsoft Power BI Community

Although the post is 5 years old little to no progress was made on this front (or I am too dumb to properly search the internet).

My MVP would be GP Date Only on the X-Axis and a dot per truck according to the waiting time in front of the gate on the Y-axis. I figured out, that a scatter chart is not the right choice and switched over to a dot plot but arriving at the same problem.