Forum Discussion

prabhatnath's avatar
prabhatnath
Advocate III
3 years ago
Solved

Represent Dynamic Table Visual based on Slicer Selection

Hi Friends,

I have a requirement where I need to represent data in the below format so what is the best visual I can use and how can I achieve the same?

Columns 1 and 3 are Fixed headers while the Value are in Columns 2 and 4 which are displayed from the Measures. The Measure values depend on the Slicer selections. The percent can be represented as a progress bar if feasible.

Possibly we can create a dynamic table visual as suggested by V-lianl-msft at another thread https://community.fabric.microsoft.com/t5/Desktop/Create-dynamic-table-based-on-the-slicer-selection/td-p/1501590  but I am not sure how we can do that? Any steps for this dynamic Table Visual will be a great help too.

 

Feature

<Value from Measure>

Description

<Value from Measure>

Start Date

<Value from Measure>

End Date

<Value from Measure>

Capacity

<Value from Measure>

Work Assigned

<Value from Measure> (%)

Completed

<Value from Measure> (%)

Pending Work

<Value from Measure> (%)

 

Thanks,

Prabhat

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi prabhatnath ,

    I updated my sample pbix file(see the attachment), please check if that is what you want.

    1. Create a dimension table as below

    2. Create a measure as below

    Measure = 
SWITCH (
    SELECTEDVALUE ( 'Table'[Type] ),
    "User Stories", [Count of User Stories],
    "Story Points", [Sum of Story Points],
    "Completed Work", [Sum of Completed Work],
    "Remaining Work", [Sum of Remaining Work]
)

    Best Regards

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi prabhatnath ,

    If I understand correctly, you want to display the second and forth field dynamically base on the slicer selection. And the forth field will display with progress format. Am I right? Could you please provide some source data in the excel files (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • prabhatnath's avatar
      prabhatnath
      Advocate III
      3 years ago

      Thanks  v-yiruan-msft for responding.

      Here is the sample data that can be used for this purpose.

      In the data "Program Name" and "Sprint Name" are used in the slicer so that report data are based on those selections.

       

      I need help adding a few summaries at the top of the report as below. Columns 1 and 3 are Fixed row headers for all selections and columns 2 and 4 values should be shown dynamically based on the Slicer selection.

      I already have the Measures for Columns 2 and 4.

      Selected SprintSprint S103Original Estimate289
      User Stories39Completed Work69
      Story Points117Remaining210

       

      Selected Sprint = Selected value from the Sprint Name Slicer.

      User Stories = Count of Records of "Work Item Type" = User Stories and "Is Current"  = True (for the selected Program and Sprint)

      Story Points  = SUM of "Story Points" where "Is Current" = True (for the selected Program and Sprint) 

      Original Estimate = SUM of "Original Estimate" where "Is Current"  = True (for the selected Program and Sprint) 

      Completed Work = SUM of "Completed Work" where "Is Current"  = True (for the selected Program and Sprint) 

      Remaining = SUM of "Remaining Work" where "Is Current"  = True (for the selected Program and Sprint) 

       

      Thanks,

      Prabhat

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi prabhatnath ,

        I created a sample pbix file(see the attachment), please check if that is what you want.

        Count of User Stories = 
CALCULATE (
    COUNT ( 'data'[Sprint Name] ),
    FILTER (
        'data',
        'data'[Work Item Type] = "User Story"
            && 'data'[Is Current] = TRUE ()
    )
)
        Sum of Story Points = 
CALCULATE (
    SUM ( 'data'[Story Points] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)
        Sum of Original Estimate = 
CALCULATE (
    COUNT ( 'data'[Original Estimate] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)
        Sum of Completed Work = 
CALCULATE (
    SUM ( 'data'[Completed Work] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)
        Sum of Remaining Work = 
CALCULATE (
    SUM ( 'data'[Remaining Work] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)

        Best Regards