cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YOGANANTHAMI
New Member

Tornado 2.1.0 Chart

Hi All, 

 

Is there any way to show the below table as Chart in Dynamic Way by slicer as County and Count by Employee. 

 

Please let me know if anyone has tried it before. 

 

Table

 

Employee ID Country Start Date End Date
1 USA 01-Jan-12 02-Feb-14
2 Poland 01-Sep-14 08-Feb-16
3 Germany 01-Jun-13 01-Aug-17
4 USA 01-Jun-21 01-Aug-23
5 Poland 01-May-12 02-Feb-14
6 Germany 01-Mar-14 08-Feb-16

 

Chart 

 


Tornado Chart
Out Year In
2012 2
2013 1
2 2014 2
2021 1
2 2016
2017 1
2023 1

 

Regards, 

Yoganantham Ilavazhagan

1 ACCEPTED SOLUTION
YOGANANTHAMI
New Member

Thank you so much for the detailed explanation. It worked!!!!. Once again, thank you so much.. 

View solution in original post

3 REPLIES 3
YOGANANTHAMI
New Member

Thank you so much for the detailed explanation. It worked!!!!. Once again, thank you so much.. 

View solution in original post

Hi @YOGANANTHAMI ,

 

Glad to help.

Would you pls mark the reply as answered to close it?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

v-kelly-msft
Community Support
Community Support

Hi  @YOGANANTHAMI ,

 

Create a calendar table as below:

calendar table = ADDCOLUMNS(CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date])),"Year",YEAR(''[Date]))

And a dimension table as below:

vkellymsft_0-1627978745902.png

 

Then create a measure as below:

Measure =
VAR _count1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Employee ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Start Date] ) = MAX ( 'calendar table'[Year] )
        )
    )
VAR _count2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Employee ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[End Date] ) = MAX ( 'calendar table'[Year] )
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Dimension'[category] ),
        "in", _count1,
        "out", _count2
    )

And you will see:

vkellymsft_1-1627978842710.pngvkellymsft_2-1627978855936.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors