Forum Discussion
Represent Dynamic Table Visual based on Slicer Selection
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
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 Sprint | Sprint S103 | Original Estimate | 289 |
| User Stories | 39 | Completed Work | 69 |
| Story Points | 117 | Remaining | 210 |
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
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
Thank you for your help on this.
Actually, I am able to get a table as normal with columns as headers and rows as values, but I was looking to represent the table as below:
Where Columns 1 and 3 are Headers/Fixed string and Columns 2 and 4 are based on Slicer selection..
Selected Sprint Sprint S103 Original Estimate 289 User Stories 39 Completed Work 69 Story Points 117 Remaining 210
Sorry if my earlier question was not clear.Thanks,
Prabhat
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