Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
I am creating a new column that gives a True/False output based on whether a particular column contains particular values.
Situation: I want to know whether a customer purchased something in the February 2016. If he did: return TRUE, if he didn't: return FALSE.
I am using the following function as input for my column:
Column1 = AND( CONTAINS(tbl_Revenue2016;tbl_Revenue2016[Customer];tbl_Customers[Customer]; CONTAINS(tbl_Revenue2016;tbl_Revenue[Date];"1-2-2016") )
The values in "tbl_Revenue[Date]" are all dates in this format: " d-M-yyyy ". If I manually change the datatype of these values to " Text ", the DAX formula does work, so it's not a formula-error.
But I don't want these dates in revenue-table to become text-types, so my only solution is to convert my value-parameter in the contains-function to a date-type.
So how can I format the value-parameter in the contains-function to a date-type (preferably with " d-M-yyyy ")?
I've tried doing both format-functions below, but these didn't work:
FORMAT("1-2-2016";"d-M-yyyy") FORMAT("1-2-2016";"d/M/yyyy")
Any help? 🙂
Best regards,
Niels
Not sure you can get there with CONTAINS using a date. Have only ever used CONTAINS with numeric and text values. You might try changing your formula to a CALCULATE using COUNT, COUTA or COUNTROWS with a FILTER statement to replace your CONTAIN statements. Oh, and wrap it all in an IF statement to get you to your true/false value.
Well, I figured out a different way while still using the CONTAINS function.
Instead, I used the DATE function to properly convert dates to the date-type:
Column1 = AND( CONTAINS(tbl_Revenue2016;tbl_Revenue2016[Customer];tbl_Customers[Customer]; CONTAINS(tbl_Revenue2016;tbl_Revenue[Date];DATE(2016;2;1)) )
This seems to work as well, as I am not getting an errormessage anymore.
Of course, with an IF statement around it to be able to get a True/False output, as you said.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |