Forum Discussion

sundials's avatar
sundials
Regular Visitor
8 months ago
Solved

Help me create this graph

Hi, I'm trying to recreate this chart here. 

But I'm failing and only get this one:

 

 

 

My summer hire column is found in my data table. I dragged it to my legends. Here's are my DAX formulas for more context:

 

Retained 3M =
VAR PeriodStart = MIN('Calendar'[Date])
VAR PeriodEnd   = MAX('Calendar'[Date])
RETURN
CALCULATE(
    COUNTROWS(Employment),
    Employment[Hire Date] >= PeriodStart &&
    Employment[Hire Date] <= PeriodEnd,
    (
        ISBLANK(Employment[Termination Date]) ||
        Employment[Termination Date] >= EDATE(Employment[Hire Date], 3)
    ),
    CROSSFILTER('Calendar'[Date], Employment[Hire Date], None)
)
 
Retention 3M = DIVIDE([Retained 3M], [Cohort Size], 0)
 
Cohort Size =
CALCULATE(
    COUNTROWS(Employment),
    Employment[Hire Date] >= MIN(Calendar[Date]) &&
    Employment[Hire Date] <= MAX(Calendar[Date])
)
 
I created a disconnected table and create retention % as measure:
 
Retention % =
VAR SelectedPeriod = SELECTEDVALUE(RetentionAxis[Period])
RETURN
SWITCH(
    SelectedPeriod,
    "3M", CALCULATE([Retention 3M]),
    "6M", CALCULATE([Retention 6M]),
    "12M", CALCULATE([Retention 12M]),
    "24M", CALCULATE([Retention 24M]),
    BLANK()
)
 
RetentionAxis =
DATATABLE(
    "Period", STRING,
    "Order", INTEGER,
    {
        {"3M", 1},
        {"6M", 2},
        {"12M", 3},
        {"24M", 4}
    }
)
 
Please help!

 

 

 

"CHART"
"DAX"
"DISCONNECTED TABLE"
"GRAPH"
  • vivien57's avatar
    vivien57
    8 months ago

    Hello

    I think, the main problem stems from the fact that your axis (the disconnected RetentionAxis table) does not filter the Calendar table, whereas your Retained 3M / Cohort Size measures use MIN/MAX(Calendar[Date]). As a result, each point on the axis returns the same period (not linked to the 3M, 6M, 12M, 24M), which produces flat lines. Furthermore, if you want multiple lines (e.g. Summer vs Non-Summer), you must use the Legend with a single measure (your ‘switched’ measure), as a Line chart does not accept multiple measures when a Legend is filled. 

    Have a nice day,

    Vivien

5 Replies

  • vivien57's avatar
    vivien57
    Super User
    8 months ago

    Hello

    I think, the main problem stems from the fact that your axis (the disconnected RetentionAxis table) does not filter the Calendar table, whereas your Retained 3M / Cohort Size measures use MIN/MAX(Calendar[Date]). As a result, each point on the axis returns the same period (not linked to the 3M, 6M, 12M, 24M), which produces flat lines. Furthermore, if you want multiple lines (e.g. Summer vs Non-Summer), you must use the Legend with a single measure (your ‘switched’ measure), as a Line chart does not accept multiple measures when a Legend is filled. 

    Have a nice day,

    Vivien

  • Irwan's avatar
    Irwan
    Super User
    8 months ago

    hello sundials 

     

    i might be misunderstood but seems what you are doing is different from what you are trying to recreate.

    the desired visual has summer/non-summer legend but your two visuals dont use the same legend.

     

    otherwise, try vivien57 's solution.

     

    Thank you.

  • d_m_LNK's avatar
    d_m_LNK
    Super User
    8 months ago

    I agree with Irwan -- the issue is that your Legend needs to the the Summer/non summer column, Y Axis - % retention, X-Axis - month retention.  

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    8 months ago

    Hi sundials ,

    I would take a moment to thank vivien57, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

     

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      8 months ago

      Hi sundials ,
      I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions