Help with a dynamic bridge please

[Naveedashraf]

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

[Greg_Deckler]

@Naveedashraf 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.

[Naveedashraf]

Thank you @Greg_Deckler .

Did that. What should be my next course of action?

[Greg_Deckler · Accepted solution]

@Naveedashraf 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))

[Naveedashraf]

ok, this looks extremely promising. Let me try and get back on this. @Greg_Deckler , so nice and helpful of you. 

[Naveedashraf]

@Greg_Deckler I'm really sorry coz these are absolutely noob questions.

I renamed Attribute to Month

I renamed Value to Status

Created 2 identical slicer tables as you instructed.

Created Var 1 and Var 2 successfully as you instructed.

What are the 'Ref' and ['Ref] here?

VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Attribute] = __Month1 && [Value] = "Open"),"__Ref",[Ref]))

 and 'Table' here is my main Data Table, right?

Can't appreciate your help enough!

[Greg_Deckler]

@Naveedashraf If you renamed your columns that way then you need this (below).Ref is a reference to your column named Ref.

Measure = 
  VAR __Month1 = SELECTEDVALUE('Slicer Table 1',[Month])
  VAR __Month2 = SELECTEDVALUE('Slicer Table 2',[Month])
  VAR __Table1 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Month] = __Month1 && [Status] = "Open"),"__Ref",[Ref]))
  VAR __Table2 = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Month] = __Month2 && [Status] = "Closed"),"__Ref",[Ref]))
RETURN
  COUNTROWS(INTERSECT(__Table1, __Table2))

[Naveedashraf]

@Greg_Deckler this worked like a charm.

The measure you gave me accurately calculates the number of items now Closed but previously Open based on the month selection.

I am going to use similar measures to calculate Newly added items as well.

Thank you so much for the handholding, making me learn and solving my issue. Perfect!

[Greg_Deckler]

@Naveedashraf No worries, have to start somewhere!