Forum Discussion
DAX Help
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.
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.
Notice: current store procedure not able to use in 'direct query' mode.
Regards,
Xiaoxin Sheng
8 Replies
- 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.
- 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")) 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
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
- 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
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.
Notice: current store procedure not able to use in 'direct query' mode.
Regards,
Xiaoxin Sheng
