Dax function TREATAS with Format Datetime

Question

I am new on DAX language, perfect if you can help

I have this code , that's give the error that we can't apply format on Treatas.

EVALUATE 
SUMMARIZE(
    TREATAS(
        VALUES(ADDCOLUMNS('Calendar', "FormattedDate", FORMAT([DateCol], "yyyy-MM-dd"))),
        ADDCOLUMNS(Contact, "FormattedDate", FORMAT(Contact[Date of Birth, "yyyy-MM-dd"))
    ),
    [FormattedDate],
    "Count of ", DISTINCTCOUNT(Contact[Id])
)

Error : Query (5, 9) Function TREATAS expects a fully qualified column reference as argument number 2.

I am doing format, because calendar[Datecol] is date and the column Contact[Date of Birth] is dateTime

score 0 · Answer 1 · answered Aug 17 '23 at 13:21

In your case, TREATAS is expecting a fully qualified column reference for the second argument, but you are providing a table expression created by ADDCOLUMNS. We use TREATAS to apply the relationships between two tables without modifying the table itself, so it expects a table with only the columns that define the relationship. I suggest that you create a new calculated column in the 'Contact' table that contains the formatted date:

   Contact[FormattedDate] = FORMAT(Contact[Date of Birth], "yyyy-MM-dd")

Then create a new calculated column in the 'Calendar' table with the same format :

   'Calendar'[FormattedDate] = FORMAT('Calendar'[DateCol], "yyyy-MM-dd")

Then :

  EVALUATE 
   SUMMARIZE(
       TREATAS(
           VALUES('Calendar'[FormattedDate]),
           Contact[FormattedDate]
       ),
       [FormattedDate],
       "Count of", DISTINCTCOUNT(Contact[Id])
   )

Dax function TREATAS with Format Datetime

1 Answers1