Forum Discussion

amy2x23's avatar
amy2x23
Frequent Visitor
10 years ago

Filter out data if on another table

I've got two queries that I've imported from SSAS, one for Quote Names and the other for Opportunity Names.  Both queries show the same Quote Number so I built a third dataset that has unique quote numbers to build a relationship between these two queries.  

 

How do I filter out the Quote Numbers in the Opportunity Names query that appear in the Quote Names query?  I've tried building the entire thing together with filters on both the Oppty Name and Quote Name but it just filters out too much information.  Basically I'm trying to find if "123 Promo" is in the Quote Name OR if it's in the Opportunity Name.  It can be in both or one or the other.

Need Help

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    amy2x23

     

    In this scenario, since you have different spell on Names in two tables, you have to filter the table based on Quote Numbers unless you keep unique format on Names. You can add a calculated column for slicer like:

     

    Column = IF(Table1[ID]=RELATED(Table2[ID]),"In","Not In")

    Then use this column in a slicer. Also create another slicer for Names. When you click "In" or "Not In", the Names slicer will be cascaded. 

     

    Regards,

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    amy2x23 Assuming you have relationships as below,

     

    QuoteNumber[QNumber] ->(1 to many) OpportunityNames[QNumber]

    QuoteNumber[QNumber] ->(1 to many) QuoteNames[QNumber]

     

    in power bi desktop simply create a slicer from table QuoteNumber with QNumber column and create one table visual for each OpportunityNames and QuoteNames table. Then simply select all values in slicer except '123 Promo' and you will see both tables filtered without that or select only '123 Promo' to find out number of rows that has that quote number.

    • amy2x23's avatar
      amy2x23
      Frequent Visitor
      10 years ago

      Yes, I have the relationship built, but filtering like that with a slicer isn't ideal being that it could be hundreds of "clicks" on the slicer.

       

      Also, the "123 Promo" has been spelled many different ways because several different people enter that information.  For example; 1.2.3. Promo, 1-2-3 Promo, 123-Promo, etc.

       

      And once I'm done corraling all this information, I need to bring in more revenue that will be coded something different like "Group A Promo" and finally a third group with "Bargain Bin Promo".

       

      Also, I know zero about DAX and I'm trying to learn that soon, however I understand SQL and Excel formulas, so I'm not completely without coding/formula knowledge.