Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Pie chart dynamic legend

Hi All, I am trying to solve a problem looks like easy but not getting any such related topic that exactly match my requirement. So posting here as a new thread.   I am dealing with Sprint data. T...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a category table and a count measure to meet your requirement:

     

     

    Then create a calculate colmun to compare the spirit,

     

    Index = VALUE(SUBSTITUTE([Sprint],"S",""))

    create the measure and put into the value field:

     

    Count = 
VAR t =
    ADDCOLUMNS (
        CALCULATETABLE ( DISTINCT ( 'Table'[WorkItem] ) ),
        "NowSprint", CALCULATE (
            MAX ( [Index] ),
            FILTER ( 'Table', 'Table'[WorkItem] = EARLIER ( [WorkItem] ) )
        ),
        "fist", CALCULATE (
            MIN ( [Index] ),
            FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = EARLIER ( [WorkItem] ) )
        )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Categories'[Category] ),
        "Previous sprint", COUNTX ( t, IF ( [NowSprint] - [fist] = 1, 1, BLANK () ) ) + 0,
        "More than 2 Sprints", COUNTX ( t, IF ( [NowSprint] - [fist] >= 2, 1, BLANK () ) ) + 0
    )

     

     


    BTW, pbix as attached.

     

    Best regards,

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

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can put the following measure into a table to meet your requirement:

     

    IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF ( category IN FILTERS ( Categories[Category] ), "Yes", BLANK () )

     


    BTW, pbix as attached.

     

    Best regards,

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

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can use the following measure to meet your requirement:

     

    IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF (
        COUNTROWS ( FILTERS ( Categories[Category] ) )
            = COUNTROWS ( ALL ( Categories[Category] ) ),
        category,
        IF ( category IN FILTERS ( Categories[Category] ), category, BLANK () )
    )

     


    BTW, pbix as attached.

     

    Best regards,

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks you for the nice solution. This is working great for static pie chart reporting.

I have another table that shows item details (just the basic table data)

As there is no relationship between main table and category if I select pie section "More than 2 Sprint" or "Previous Sprint", it does not change the items based on selection. if there is some pissibility to filter the table based on Pie chart selection that will be great, if not then I will go with this.

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can put the following measure into a table to meet your requirement:

 

IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF ( category IN FILTERS ( Categories[Category] ), "Yes", BLANK () )

 


BTW, pbix as attached.

 

Best regards,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you, that works as expected.

    Only a small glitch is if I do not select sny sprint so by default everything is selected, then the main table gives me record only with carryover data. D, E never appear in the table as they were never carried over. But as I have not selected the Old Work (Carry Over) Pie chart, it should show me all the data.

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      We can use the following measure to meet your requirement:

       

      IsInCategory =
VAR NowSprint =
    MAX ( 'Table'[Index] )
VAR first =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[WorkItem] = MAX ( 'Table'[WorkItem] ) )
    )
VAR category =
    IF (
        NowSprint - first = 1,
        "Previous sprint",
        IF ( NowSprint - first >= 2, "More than 2 Sprints", "Now Sprint" )
    )
RETURN
    IF (
        COUNTROWS ( FILTERS ( Categories[Category] ) )
            = COUNTROWS ( ALL ( Categories[Category] ) ),
        category,
        IF ( category IN FILTERS ( Categories[Category] ), category, BLANK () )
    )

       


      BTW, pbix as attached.

       

      Best regards,

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        It worked great, thank you