Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIn 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThat worked! thank you!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
102 | |
81 | |
66 |