Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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))
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you Greg_Deckler .

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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!