Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
09-17-2017 11:35 AM - last edited 10-28-2019 15:51 PM
Name:
Text Gantt Chart
Description:
Variation of the Chris Webb's great Star Ratings Quick Measure that creates a text gantt chart
http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903
You can choose the characters of the chart however the you need to be careful to have a space that is the same width.
These characters only work with a monospaced font like consolas or Courier New
VAR __BLANKCHAR = 32 -- Space VAR __FULLCHAR = 9679 -- Dot
You can use a number of unicode emoji characters with a variable width and Ideographic Space is close width but will be totally accurate.
VAR __BLANKCHAR = 12288 -- Ideographic Space VAR __FULLCHAR = 128307 -- Full Block VAR __FULLCHAR = 128308 -- Black Circle VAR __FULLCHAR = 128310 -- Shape VAR __FULLCHAR = 11035 -- Black Square VAR __FULLCHAR = 128523 -- Smiley
Parameters:
Name: Start Time
Tooltip : Value of the Item/Task Start Time
Type: Numerical field / measure
Name: Duration
Tooltip : Value of the Item/Task Duration
Type: Numerical field / measure
Name: Maximum Duration
Tooltip : The Maximum duration used to scale the chart
Type: Numerical field / measure
DAX:
GanttDot = VAR __MAX_NUMBER_OF_BLOCKS = 100 VAR __MIN_RATED_VALUE = 0 VAR __STARTBASE_VALUE = MIN(Durations[StartTime]) VAR __BASE_VALUE = Sum(Durations[Minutes]) VAR __MAX_RATED_VALUE = CALCULATE(MAX(Durations[EndTime]), ALL(Durations)) -- Monospace characters VAR __BLANKCHAR = 32 -- Space VAR __FULLCHAR = 9679 -- Dot VAR __NORMALIZED_STARTBASE_VALUE = MIN( MAX( DIVIDE( __STARTBASE_VALUE - __MIN_RATED_VALUE, __MAX_RATED_VALUE - __MIN_RATED_VALUE ), 0 ), 1 ) VAR __NORMALIZED_BASE_VALUE = MIN( MAX( DIVIDE( __BASE_VALUE - __MIN_RATED_VALUE, __MAX_RATED_VALUE - __MIN_RATED_VALUE ), 0 ), 1 ) VAR __START_RATING = ROUND(__NORMALIZED_STARTBASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0) VAR __BASE_RATING = MAX(ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0),1) RETURN IF( NOT ISBLANK(__BASE_VALUE), REPT(UNICHAR(__BLANKCHAR), __START_RATING) & REPT(UNICHAR(__FULLCHAR), __BASE_RATING) )
GanttBlock = VAR __MAX_NUMBER_OF_BLOCKS = 100 VAR __MIN_RATED_VALUE = 0 VAR __STARTBASE_VALUE = MIN(Durations[StartTime]) VAR __BASE_VALUE = Sum(Durations[Minutes]) VAR __MAX_RATED_VALUE = CALCULATE(MAX(Durations[EndTime]), ALL(Durations)) -- Variable width characters VAR __BLANKCHAR = 12288 -- Ideographic Space VAR __FULLCHAR = 128307 -- Full Block VAR __NORMALIZED_STARTBASE_VALUE = MIN( MAX( DIVIDE( __STARTBASE_VALUE - __MIN_RATED_VALUE, __MAX_RATED_VALUE - __MIN_RATED_VALUE ), 0 ), 1 ) VAR __NORMALIZED_BASE_VALUE = MIN( MAX( DIVIDE( __BASE_VALUE - __MIN_RATED_VALUE, __MAX_RATED_VALUE - __MIN_RATED_VALUE ), 0 ), 1 ) VAR __START_RATING = ROUND(__NORMALIZED_STARTBASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0) VAR __BASE_RATING = MAX(ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_BLOCKS, 0),1) RETURN IF( NOT ISBLANK(__BASE_VALUE), REPT(UNICHAR(__BLANKCHAR), __START_RATING) & REPT(UNICHAR(__FULLCHAR), __BASE_RATING) )
There are several examples of differerent characters in the example document. I've also included a stacked bar chart version for comparison.
Also included is a what if screen and a searchable list of to emoji chars to help pic the a character. Thanks to Hugoberry's tip on converting hex to decimal
Expression.Evaluate(“0x”&input)
I wrote this dax for the gantt chart in my SSIS DB Dashboard data story gallery entry/
http://community.powerbi.com/t5/Data-Stories-Gallery/SSIS-Catalog-DB-Dashboard/m-p/244677
Cheers
Phil
eyJrIjoiOTI4ZWM5NjctZDgwNS00N2VhLWFhOWEtZDFjMDE2ZWRhOTBiIiwidCI6ImEwYTcyYzIzLTdlMWEtNGYxOC05NDU4LTlhNzUyYTEzMTg1NCJ9