Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using CONTAINS to filter a column

Hi All
 
I am trying to sum a column of $ values with a filter being applied to 2 other columns. 
e.g., I want to sum the sales revenue column when the Intake column CONTAINS the word Intensive and the student booking date is greater than Jun 21. 
Below is what I have but it isn't bringing any values through. 
I have read a few posts and tried a few different things but still no luck. 
Any assistance is greatly appreciated. 
 
Sales Revenue Intensive only > Jun21 =
CALCULATE (
SUM ( 'Sales Reports'[Sales Revenue] ),
CONTAINSSTRING('Sales Reports'[Intake],"Intensive"),
'Sales Reports'[Student Booking Date] > DATE ( 2021, 5, 31 )
)
 
Cheers
Karen
using
  • VahidDM's avatar
    VahidDM
    5 years ago

    Hi Anonymous 

     

    You measure is correct and works well.

    I checked that with sample data and resualt for >Jun21 and >Jul21 are as below:

     

     

    The measure amitchandak shared has more advantages for you as you can see that on the table.

    Please share the data in the table format to be able to review that.

     

    Did I answer your question? Mark my post as a solution!

    Appreciate your Kudos  !!

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try with small change

     

    CALCULATE (
    SUM ( 'Sales Reports'[Sales Revenue] ),
    filter('Sales Reports', CONTAINSSTRING('Sales Reports'[Intake],"Intensive") &&
    'Sales Reports'[Student Booking Date] > DATE ( 2021, 5, 31 )
    ))

     

     

    or

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      VahidDM Thank you. I re checked my measure and it appears that I had mysteriously put a comma somewhere that shouldn't have been there. 😞 Apologies for this. Thank you for taking the time to check it. 

      Cheers

      Karen

  • VahidDM's avatar
    VahidDM
    Super User
    5 years ago

    Hi Anonymous 

     

    You measure is correct and works well.

    I checked that with sample data and resualt for >Jun21 and >Jul21 are as below:

     

     

    The measure amitchandak shared has more advantages for you as you can see that on the table.

    Please share the data in the table format to be able to review that.

     

    Did I answer your question? Mark my post as a solution!

    Appreciate your Kudos  !!