DAX not letting me use column ref

[AbbyLear]

I think I have a simple question but for the life of me, cannot figure out. 

I have a table which has the following fields:

- Date

- Year (of the date) - added through transform data

- Quarter (of the date) - added through transform data

I want to add a column, or create a measure which has "YYYY QQ" format. For example, of date is 01/01/2022, I want the new column/measure to be "2022 Q1". 

I tried adding a measure with DAX formula "FORMAT" - won't let me select "Date" as column Reference. I tried using "&", won't let me select the "Year" and "Quarter" in there. I tried adding a column with power query, but not too familar so failed as well. 

On top of all this, I had successfully performed this before in another table in the same workbook which worked: (so this is driving me crazy why it won't work anymore)

Year Quarter = Calendar_Lookup_Pmt[Year] & " Q" & Calendar_Lookup_Pmt[Quarter]

So I am really lost as to why this is not happening... Thanks for any help you can provide. 

[edhans]

It sounds like you want a Calculated Column here, not a Measure. A measure must operate tables of data - even if a column. So you cannot say = FORMAT(Table[Field], "#") for example because even if that is a one record table, DAX cannot figure out how to represent that info, it needs an aggregator, like SUM.

But in a Calculated Column, it does operate on one row at a time. Go to the Data view, click on the table you want to do this in, then in the ribbon in Column tools, add a column.

[edhans · Accepted solution]

In a Calculated Column @AbbyLear you don't use Power Query M code, you use DAX. So your formula Year Quarter = Calendar_Lookup_Pmt[Year] & " Q" & Calendar_Lookup_Pmt[Quarter] would work fine.

If you wanted to use Power Query - which I think is the better choice for adding columns, use this in a Custom Column:

=Text.From([Year]) & "Q" & Text.From([Qtr])

Unlike DAX, in Power Query you do not include the table name when referencing columns since you can only directly reference columns of the table you are in.

[AbbyLear]

That worked! thank you! 

[AbbyLear]

Great! That makes sense (though no clue how I was able to do it before) 

when I create the calculated column, what power query formula should I use? I tried a couple and didn't work (text.combine etc.) keeps giving me error messages. 

[Ashish_Mathur]

Hi,

If you want to do this in the Query Editor, then try this

=Number.FromText(Calendar_Lookup_Pmt[Year])& " Q" & Number.FromText(Calendar_Lookup_Pmt[Quarter])

Hope this helps.