DAX Help

[Anonymous]

Hello everyone,

New to PBI and not sure how to get the syntax right for my simple need.

I have a date column "DATE" with dates ranging from year 2017-2020.

Want to create a new column based on "DATE" column's year.

say for date (dd-mm-yyyy) -

01-01-2017 the new column should reflect mynaming_2017,

01-01-2018 the new column should reflect mynaming_2018 and so on.

where, mynaming is something i would like to concatenate the year part with.

Please help with the right approach.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Maybe you can try to use t-sql query in your connector to add a custom column that formats your date fields and concatenate with current username.

SYSTEM_USER (Transact-SQL) 

Notice: current store procedure not able to use in 'direct query' mode.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks everyone for your help.

Using M query approach works but needs me to switch the connection type to import which is less perferred due to data size.

Using DAX approach I am unable to use FORMAT fucntion on direct query mode.

Error says: FORMAT is not allowed as part of calculated columns DAX expressions on direct query models.

Can you please help  @amitchandak @AllisonKennedy 

[AllisonKennedy]

@Anonymous
Yes, most Text functions are not available in Direct Query mode when used in Calculated Columns or RLS.
Do you have a DimDate table? What is the ultimate goal? Import isn't always a bad thing on large datasets, especially now that we can use incremental refresh and dataflows, and we have all the added functionality you're missing with Direct Query.
https://radacad.com/directquery-live-connection-or-import-data-tough-decision
https://blog.pragmaticworks.com/import-vs-directquery-storage-mode
https://radacad.com/getting-started-with-dataflow-in-power-bi-part-2-of-dataflow-series
https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only

[amitchandak]

@Anonymous , You can use the format

make sure date is detected as a date -https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/599712

New date = "MyFormat" & format([Date], "MM-YYYY")

Check the various format

https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

[AllisonKennedy]

@Anonymous
I personally prefer to do calculated columns in Power Query, so if you click Transform Data in the Home tab in the ribbon you can open Power Query Editor. This will give you an Add Column tab in the ribbon. If you select your date column, in the Add Column tab on the right there's a Date button, then select Year.

Next, still in the Add Column tab in the ribbon, there's a Column From Examples tab. Select the Year column and click the Column From Examples, From selected. Then type your mynaming_2017 for the first couple rows and Power BI should create the M code to concatenate them as you need. Just double check that the M code makes sense and is using that Year column.

To do this using DAX (it will slow down filter/slicer performance slightly) but you can try:

CustomYear = COMBINEVALUES("_", "mynaming", FORMAT(Date[Date], "YYYY"))

[pranit828]

Hi @Anonymous 

Create duplicate column in Power Query Editor of the date column and transform as below.

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

Regards,
Pranit

[Anonymous]

Hello Pranit,

Thanks for the reply,

I have extracted the datepart from the date field.

Not sure how i can add a dummy column with any random value (say mytablename) and then later concatenate this value with datepart value.

expected output for year 2017 would be

mytablename_2017

[AllisonKennedy]

@Anonymous
you can add a dummy column with random value by using Add Column > Add Custom Column

then put ="Dummy Value"
in the box for the formula. Otherwise see my other post.