Forum Discussion
Help with a dynamic bridge please
Hello,
I am very new to PBI - so your patience and help with my supposedly basic request is highly appreciated.
I have the following table.
Ref is unique text. Tasks are unique too. I need to geta a dynamic bridge (either as number cards or waterfall) where I show "between any two selected months", the "new tasks added", "less tasks closed i.e. previously open and now closed" to arrive at the closing number of open tasks. I've been thinking of columns, dynamic measures, SWITCH function etc. - don't know how to achieve the objective.
| Ref | Tasks | Aug Status | Sep Status | Oct Status |
| 5 | A | Open | Closed | Closed |
| 7 | B | Open | Closed | Closed |
| 15 | F | Open | Open | Closed |
| 17 | G | Open | Open | |
| 19 | H | Open | Closed | |
| 21 | I | Open | ||
| 23 | J | Open |
Anonymous OK, create 2 disconnected tables (no relationships). Let's say you didn't rename the Attribute and Value columns after unpivoting, create your slicer tables like this:
Slicer Table 1 = DISTINCT('Table'[Attribute])Slicer Table 2 = DISTINCT('Table'[Attribute])Now, create a measure like this:
Measure = VAR __Month1 = SELECTEDVALUE('Slicer Table 1',[Attribute]) VAR __Month2 = SELECTEDVALUE('Slicer Table 2',[Attribute]) VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Attribute] = __Month1 && [Value] = "Open"),"__Ref",[Ref])) VAR __Table2 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Attribute] = __Month2 && [Value] = "Closed"),"__Ref",[Ref])) RETURN COUNTROWS(INTERSECT(__Table1, __Table2))
8 Replies
Anonymous Unpivot your Status columns in Power Query Editor. Would also be helpful to have an actual date column or at least a month number column.
Thank you Greg_Deckler .
Did that. What should be my next course of action?
Anonymous OK, create 2 disconnected tables (no relationships). Let's say you didn't rename the Attribute and Value columns after unpivoting, create your slicer tables like this:
Slicer Table 1 = DISTINCT('Table'[Attribute])Slicer Table 2 = DISTINCT('Table'[Attribute])Now, create a measure like this:
Measure = VAR __Month1 = SELECTEDVALUE('Slicer Table 1',[Attribute]) VAR __Month2 = SELECTEDVALUE('Slicer Table 2',[Attribute]) VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Attribute] = __Month1 && [Value] = "Open"),"__Ref",[Ref])) VAR __Table2 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Attribute] = __Month2 && [Value] = "Closed"),"__Ref",[Ref])) RETURN COUNTROWS(INTERSECT(__Table1, __Table2))