Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Thank you so much for the detailed explanation. It worked!!!!. Once again, thank you so much..
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!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |