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 trying to write the basic form of a measure to look at the sum of a value before a product was launched. So the pseudocode for it would be:
 
SUM Number of Contacts WHERE ContactsData Year-Week < ProductLaunch Year-Week.
 
I added a column to both tables which has the year week in a whole number format of yyyyww (e.g 202123). Here is the DAX code for what I have so far:

Text

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

RETURN

CALCULATE(
    SUM('CS Data'[No of Contacts Answered]),
    FILTER('CS Data','CS Data'[YW] < ProductYW)
This currently doesn't work and just shows blank. HOWEVER, if I change the < to = then it does work, accurately showing the sum of contacts for the product activity year-week.
 
My thinking is that it has something to do with the fact that in the Conacts Data table there are lots of rows which will be having the same year-week but that is why I am using the filter method since it should be summing all contacts where the year week is less than the product year week.
 
Any ideas?
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.

     

     

12 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    Don't know how you are presenting the information in your report but believe this is related with the type of filtering you are doing, that can be impacted by other context (filters, columns, values).

     

    Try the following change to your formula:

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

RETURN

CALCULATE(
    SUM('CS Data'[No of Contacts Answered]),
    FILTER(ALL('CS Data''CS Data'[YW]),'CS Data'[YW] < ProductYW)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Miguel,

       

      Thanks for your suggestion.

       

      The syntax didn't seem to work when copying in your solution. I tried to change it to this but this produces the same results as before:

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

RETURN

CALCULATE(
    SUM('CS Data'[No of Contacts Answered]),
    FILTER(
        ALL('CS Data'),
        'CS Data'[YW] < ProductYW
    )
)
      • 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.