topic Re: Using CONTAINS to filter a column in DAX Commands and Tips

Using CONTAINS to filter a column

Anonymous — Tue, 17 Aug 2021 01:39:00 GMT

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

Re: Using CONTAINS to filter a column

amitchandak — Tue, 17 Aug 2021 04:03:36 GMT

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 )
))

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

Re: Using CONTAINS to filter a column

VahidDM — Tue, 17 Aug 2021 04:16:53 GMT

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 !!

Re: Using CONTAINS to filter a column

Anonymous — Tue, 17 Aug 2021 06:45:03 GMT

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