Shared X axis for multiple dates and Count of occurrence on Y axis

[hamadani]

Hi,

I have a table similar to below

Case ID	Deadline 1	Deadline 2	Deadline 3	Deadline 4
A	Dec 2022	Feb 2023	May 2023	Jun 2024
B		Jul 2020	May 2023	Aug 2024
C	May 2019	Jul 2020	Dec 2021
D	Jul 2020	Mar 2021	Apr 2021	Aug 2024
E	May 2019		May 2023	Aug 2024
F	Dec 2022	Feb 2023	May 2023	Aug 2024
G	Dec 2022	Feb 2023	May 2023	Aug 2024

I would like to have a graph with single x axis as date (from min(date) of above table to max(date)) and then number of cases who have a deadline for a given date.  So, I am expecting something similar to below:

Thanks

[v-yadongf-msft · Accepted solution]

Hi @hamadani ,

This is my test table:

Select "Case ID" and Unpivot other columns:

You will get a table like this:

Close and apply to Power BI, create a measure:

Count of case =
CALCULATE (
    COUNT ( 'Table'[Case ID] ),
    FILTER ( 'Table', 'Table'[Value] = SELECTEDVALUE ( 'Table'[Value] ) )
)

Create a scatter visual:

I think this is the result you want.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hamadani]

Thank you @amitchandak . However, could you please elaborate on what you mean by "joining all of them"? All data is stored in one table 

[amitchandak]

@hamadani , Join all of them with a common date table and use date from date table on axis.

Other than one join all other joins will be inactive, use userelationship for that

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...