Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

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

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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:

     

    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:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • v-kelly-msft's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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!