Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter multiple rows against another table using less than

Apologies for the awkward title, I am struggling to work out how to do this or even word the problem. I have a feeling it should be fairly straightforward but I am hitting a roadblock. So I'm t...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:

     

    The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:

    Pre-Launch Contacts = 
VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0)

RETURN

CALCULATE(
    SUM('CS Data'[Contacts Answered]),
    FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))

     

    You can see that when you cross filter this with the YW of the product dates you get the results you need.

     

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous 

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ，

    I tried to create a table visual which apply your measure [Pre-Launch Contacts] and the field [YW] of table Product Activity on it base on your provided excel data, it can return the result just as shown in below screenshot.

    How did you set the visual? Did you put the field [YW] of table Product Activity on your visual?

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Anonymous ,


      Thanks for your support, I really appreciate it.

       

      The visual I am trying to create is as follows:

       

      • Each row should be the product activity description
      • Columns for each row will show the PA launch week (from PA table) and then the sum of contacts from the contacts table from before the launch week.

      As I said, I can get this to work when making a measure which looks at the sum of contacts that is EQUAL to the launch week but not a less than or greater than.

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi   Anonymous .

         

        First of all looking at your data you have an issue with the YW column in the Product activity you have the week 8 and the value is 20218 and for the week 10 forward you will have with 202120 so has you can see the size of the number are different so you can have difficulties comparing for example products launched in the first 9 weeks of a year with the previous values.

        For this you need to have the YW with 6 digits so it would be 202108.

         

        I have a question regarding the date of the launch in this case your lower date is 23rd february in week 8, however your contacts table show information starting on week 17 how are you identifying the product launch?