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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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  @Anonymous ,

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.