Conditional Column using date from another table

[Anonymous]

I'm building a Copilot report, using CopilotReadinessActivityUserDetail information, and joining with user and date enabled for Copilot which is coming from another table. 

In the CopilotReadinessActivityUserDetail table, I have a column called "Report Refresh Date", which is the extract data for the data. Every row hasd the same value, so for example I can see the date uploaded on a card with a measure of 

CADate = LASTDATE(CopilotActivityUserDetail[Report Refresh Date]). 

In my UserDateAuthorized table, I have a date where my user was granted a Copilot license, simply with 3 columns, UserPrincipalName, IsMember and DateAuthorized. I want to add a custom field to this table of DaysSinceAuthorized, which calculates LASTDATE(CopilotActivityUserDetail[Report Refresh Date]) - [DateAuthorized].

I tried a variety of ways to do this - e.g. 

= Table.AddColumn(#"Added Custom", "DaysSinceAuthorized", each if [IsMember] = 1 then
Duration.Days(DateTime.Date(LASTDATE(CopilotActivityUserDetail[Report Refresh Date])) - DateTime.Date([Date Added]))
else null)

... giving Expression.Error: The name 'LASTDATE' wasn't recognized. Make sure it's spelled correctly.

or 

= Table.AddColumn(#"Added Custom", "DaysSinceAuthorized", each if [IsMember] = 1 then
Duration.Days(DateTime.Date(CALCULATE(MAX((CopilotActivityUserDetail[Report Refresh Date])))) - DateTime.Date([Date Added]))
else null)

... giving Expression.Error: The name 'CALCULATE' wasn't recognized. Make sure it's spelled correctly.

or 

= Table.AddColumn(#"Added Custom", "DaysSinceAuthorized", each if [IsMember] = 1 then
Duration.Days(DateTime.Date(CopilotActivityUserDetail[Report Refresh Date]) - DateTime.Date([Date Added]))
else null)

... compiling, but giving Error in every value. 

Whar am I doing wrong?

[Anonymous · Accepted solution]

Hi @Anonymous ,

From the error messages you've shared, it seems like you're attempting to use DAX functions within Power Query's M language environment, which will not work since these are separate environments with different functions and syntaxes.
Please try like:

if [IsMember]=1 then Duration.TotalDays(List.Max(#"CopilotReadinessActivityUserDetail"[#"Report Refresh Date"]) - [DateAuthorized]) else null

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Anonymous]

Thanks so much for this, and it fixed it for me. I broke out the steps to see what was happening, as follows:

1. Added Custom1:

= Table.AddColumn(#"Added Custom", "CopilotActivityLatestDate", each
    List.Max(#"CopilotReadinessActivityUserDetail"[#"Report Refresh Date"])
  )

2. Added Custom2:

= Table.AddColumn(#"Added Custom1", "DaysSinceAdded", each
    if [IsMember]=1 then
      Duration.Days(DateTime.Date([CopilotActivityLatestDate]) - DateTime.Date([DateAdded]))
    else null
  )

3. Changed Type:

= Table.TransformColumnTypes(#"Added Custom2",{{"DaysSinceAdded", type number}})

The List.Max syntax was the key thing for me, and understanding M syntax versus DAX. Also the way to quote a literal for the other table, then the other table's column - as #"OtherTable"[#"OtherColumn"]. 

[lbendlin]

You seem to be trying to run DAX commands inside of Power Query.  While that is possible to some extent, it's not done the way you are trying here.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...