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...
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))
Anonymous 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))
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!
Anonymous No worries, have to start somewhere!