Solved: Line chart

unnijoy · ‎02-21-2019

I need to creata a line chart from the below table. Line 1 should give me the distint count of user name based on True in column named "Learning Plan Completed". And Line 2 should give me distint count of user name based on Yes in column named "Course COMPLETED". Here am facing an issue with a common date for setting as X axis. i got two dates column named "Learning Plan Completed Date" & "Course DATE_COMPLETED. i need to get a idea to get month on X axis and it should have a connection with colums "Learning Plan Completed Date" & "Course DATE_COMPLETED".

USERNAME	Learning Plan Completed	Learning Plan Completed Date	Course COMPLETED	Course DATE_COMPLETED
CHRISTOPHER.PEERS	FALSE		NO
danielle.mclarnon	FALSE		YES	02/08/2019
leighann.worrall	FALSE		NO
rebecca.curran	TRUE	02/06/2019	YES	01/10/2019
steven.fraser	FALSE		YES	02/11/2019
john.campbell	FALSE		NO
cormac.sweeney	FALSE		YES	01/16/2019
ANDREW.SCHOFIELD	FALSE		NO
danielle.mclarnon	FALSE		NO
CHRISTOPHER.PEERS	FALSE		NO
leighann.worrall	FALSE		NO
rebecca.curran	TRUE	02/06/2019	YES	01/31/2019
steven.fraser	FALSE		NO
john.campbell	FALSE		NO
cormac.sweeney	FALSE		NO
ANDREW.SCHOFIELD	FALSE		NO
danielle.mclarnon	FALSE		NO
danielle.mclarnon	FALSE		NO
CHRISTOPHER.PEERS	FALSE		NO
CHRISTOPHER.PEERS	FALSE		NO
rebecca.curran	TRUE	02/06/2019	YES	01/18/2019
steven.fraser	FALSE		NO
john.campbell	FALSE		NO
cormac.sweeney	FALSE		NO
ANDREW.SCHOFIELD	FALSE		NO
leighann.worrall	FALSE		NO
rebecca.curran	TRUE	02/06/2019	NO
john.campbell	FALSE		YES	06/15/2018
steven.fraser	FALSE		NO
leighann.worrall	FALSE		NO
ANDREW.SCHOFIELD	FALSE		NO
cormac.sweeney	FALSE		NO
danielle.mclarnon	FALSE		YES	06/15/2018
CHRISTOPHER.PEERS	FALSE		NO
john.campbell	FALSE		NO
ANDREW.SCHOFIELD	FALSE		YES	04/23/2017
cormac.sweeney	FALSE		YES	12/05/2018
steven.fraser	FALSE		YES	02/20/2018
leighann.worrall	FALSE		NO
rebecca.curran	TRUE	02/06/2019	YES	01/30/2019
danielle.mclarnon	FALSE		NO

v-yulgu-msft · ‎02-21-2019

Hi @unnijoy ,

New a calendar table which is unrelated to fact data table.

Date dim =
CALENDAR (
    MIN (
        MIN ( Sheet5[Learning Plan Completed Date] ),
        MIN ( Sheet5[Course DATE_COMPLETED] )
    ),
    MAX (
        MAX ( Sheet5[Learning Plan Completed Date] ),
        MAX ( Sheet5[Course DATE_COMPLETED] )
    )
)

Add [Date] from 'Date dim' table onto chart axis. Create measures as below to get the distinct count of user name.

Count True =
CALCULATE (
    DISTINCTCOUNT ( Sheet5[USERNAME] ),
    FILTER (
        ALL ( Sheet5 ),
        Sheet5[Learning Plan Completed] = "TRUE"
            && Sheet5[Learning Plan Completed Date].[Year]
                = SELECTEDVALUE ( 'Date dim'[Date].[Year] )
            && Sheet5[Learning Plan Completed Date].[MonthNo]
                = SELECTEDVALUE ( 'Date dim'[Date].[MonthNo] )
    )
)

Count Yes =
CALCULATE (
    DISTINCTCOUNT ( Sheet5[USERNAME] ),
    FILTER (
        ALL ( Sheet5 ),
        Sheet5[Course COMPLETED] = "YES"
            && Sheet5[Course DATE_COMPLETED].[Year] = SELECTEDVALUE ( 'Date dim'[Date].[Year] )
            && Sheet5[Course DATE_COMPLETED].[MonthNo]
                = SELECTEDVALUE ( 'Date dim'[Date].[MonthNo] )
    )
)

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

unnijoy · ‎02-21-2019

Hi @v-yulgu-msft

Thanks for your reply. For the graph is ging blank.

Do i need to set the relationship with new table and existing table fields