Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
hamadani
Frequent Visitor

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

Hi,

I have a table similar to below

Case ID      Deadline 1       Deadline 2    Deadline 3    Deadline 4     
ADec 2022Feb 2023May 2023Jun 2024
B Jul 2020May 2023Aug 2024
CMay 2019Jul 2020Dec 2021 
DJul 2020Mar 2021Apr 2021Aug 2024
EMay 2019 May 2023Aug 2024
FDec 2022Feb 2023May 2023Aug 2024
GDec 2022Feb 2023May 2023Aug 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:

hamadani_1-1668078588945.png

 

Thanks

 

1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community Support

Hi @hamadani ,

 

This is my test table:

vyadongfmsft_0-1668413758842.png

 

Select "Case ID" and Unpivot other columns:

vyadongfmsft_1-1668413869590.png

 

You will get a table like this:

vyadongfmsft_2-1668413903659.png

 

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:

vyadongfmsft_3-1668414148191.png

 

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.

View solution in original post

3 REPLIES 3
v-yadongf-msft
Community Support
Community Support

Hi @hamadani ,

 

This is my test table:

vyadongfmsft_0-1668413758842.png

 

Select "Case ID" and Unpivot other columns:

vyadongfmsft_1-1668413869590.png

 

You will get a table like this:

vyadongfmsft_2-1668413903659.png

 

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:

vyadongfmsft_3-1668414148191.png

 

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
Frequent Visitor

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
Super User
Super User

@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...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.