Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Trouble filtering with multiple fields

OK, so I have two tables of financial data: Table 1 Code     Date      Revenue1     Revenue 2   Tabel 2 Code     Date      Revenue_Planned1       Revenue_Planned2   I want to slice both tables...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a medium table to be the slicer.

     

    1. create calculated table.

     

    MonthTable = 
DISTINCT(UNION(DISTINCT(Table1[Date]),DISTINCT(Table2[Date])))
    CodeTable = 
DISTINCT(UNION(DISTINCT(Table1[Code]),DISTINCT(Table2[Code])))

    2. create relation ship between four table

     

    3. use the column in medium table as the silcer.

     

    4. create measure using the source table.

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I'm sorry, this does't work at all.

 

This just moves the conflict from the Code field to the Date field, with the same issue.

"You can't create a direect active relationship between Table 1 and Table 2, becuase that wold create ambiguity between Date and Table 1" 

  • JosefPrakljacic's avatar
    JosefPrakljacic
    Solution Sage
    6 years ago

    Hi,

     

    may I ask you to provide some sample data? So that we don't need to guess.

     

    BR,

    Josef

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Table 1
      Code         Date      Revenue1     Revenue 2

      Client A     Jan         $1000            -$500
      Client B     Jan         $2000            $500
      Client C     Jan         $1500            $50
      Client D     Jan         $800             

      Client A     Feb        $1500            $800
      Client B     Feb        $1000            $50
      Client C     Feb        $100             $500
      Client D     Feb        $800             $200

       

       

       

      Tabel 2
      Code          Date      Revenue_Planned1       Revenue_Planned2

      Client A     Jan         $2000                           $500
      Client B     Jan         $1000                           $1500
      Client C     Jan         $1500                           $500
      Client D     Jan         $800                             $200

      Client A     Feb        $1500                           $100
      Client B     Feb        $1000                           $50
      Client C     Feb        $100                             $500
      Client D     Feb        $800                             $200


      So I want to see
      Date Slicer: JAN
      Code Slicer: Client C

      Planned Revenue: $2000
      Actual Revenue     $1550

       

      And to be able to change those 2 slicers to provide the data for one of more clinets and one or more dates.
      Note that merging all possible dates and codes is not a tenable solution.

       

       

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        We can create a medium table to be the slicer.

         

        1. create calculated table.

         

        MonthTable = 
DISTINCT(UNION(DISTINCT(Table1[Date]),DISTINCT(Table2[Date])))
        CodeTable = 
DISTINCT(UNION(DISTINCT(Table1[Code]),DISTINCT(Table2[Code])))

        2. create relation ship between four table

         

        3. use the column in medium table as the silcer.

         

        4. create measure using the source table.

         

        BTW, pbix as attached.

         

        Best regards,

        Community Support Team _ Dong Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.