Forum Discussion
Helper IVPopulating a new column from a measure
I have a measure which counts the number of stores opening on certain months and i was wanting to create a new column with these values if it is possible ?
Any suggestions ?
Yes, you can create a new column in Power BI using the values from a measure, but it requires a bit of a workaround since measures and columns behave differently. Here’s a step-by-step guide to help you achieve this:
Create a Calculated Column:
- Go to the “Modeling” tab in Power BI Desktop.
- Select “New Column”.
- Use a DAX formula to create the column. For example, if your measure is called StoreOpenings, you can use a formula like:
StoreOpeningsColumn = [StoreOpenings]
- This will create a new column with the values from your measure.
Using a Calculated Table:
- Sometimes, creating a calculated table might be more appropriate, especially if you need to aggregate data differently.
- You can create a calculated table using the SUMMARIZE function to group your data by month and then add the measure values.
StoreOpeningsTable = SUMMARIZE( 'YourTable', 'YourTable'[Month], "StoreOpenings", [StoreOpenings] )
Using Variables in Measures:
- If you need to use the measure in a more complex calculation, you can define variables within your measure to store intermediate results.
StoreOpeningsWithVariable = VAR Openings = [StoreOpenings] RETURN Openings
- If you need to use the measure in a more complex calculation, you can define variables within your measure to store intermediate results.
These methods should help you incorporate the measure values into a new column or table. If you need more detailed guidance, there are many tutorials and community forums that can provide additional examples and support.
Also Follow given below link, i hope this will help you:
Solved: Turning a measure into a column - Microsoft Fabric Community
Solved: Column Based on Values From a Measure - Microsoft Fabric Community
3 Replies
- 123abc
Community Champion
Yes, you can create a new column in Power BI using the values from a measure, but it requires a bit of a workaround since measures and columns behave differently. Here’s a step-by-step guide to help you achieve this:
Create a Calculated Column:
- Go to the “Modeling” tab in Power BI Desktop.
- Select “New Column”.
- Use a DAX formula to create the column. For example, if your measure is called StoreOpenings, you can use a formula like:
StoreOpeningsColumn = [StoreOpenings]
- This will create a new column with the values from your measure.
Using a Calculated Table:
- Sometimes, creating a calculated table might be more appropriate, especially if you need to aggregate data differently.
- You can create a calculated table using the SUMMARIZE function to group your data by month and then add the measure values.
StoreOpeningsTable = SUMMARIZE( 'YourTable', 'YourTable'[Month], "StoreOpenings", [StoreOpenings] )
Using Variables in Measures:
- If you need to use the measure in a more complex calculation, you can define variables within your measure to store intermediate results.
StoreOpeningsWithVariable = VAR Openings = [StoreOpenings] RETURN Openings
- If you need to use the measure in a more complex calculation, you can define variables within your measure to store intermediate results.
These methods should help you incorporate the measure values into a new column or table. If you need more detailed guidance, there are many tutorials and community forums that can provide additional examples and support.
Also Follow given below link, i hope this will help you:
Solved: Turning a measure into a column - Microsoft Fabric Community
Solved: Column Based on Values From a Measure - Microsoft Fabric Community
Jay2022Thanks in your 2nd example what does the "storeopenings" in brackets refer to please ? STruggling to get P Bi to accept my code when i try this
StoreOpeningsTable = SUMMARIZE( 'YourTable', 'YourTable'[Month], "StoreOpenings", [StoreOpenings] )- 123abc
In the SUMMARIZE function, "StoreOpenings" in brackets refers to the name of the new column that will be created in the summarized table. The [StoreOpenings] part is supposed to be the measure or column that you want to summarize.
Here’s a breakdown of the SUMMARIZE function:
- 'YourTable': The table you are summarizing.
- 'YourTable'[Month]: The column by which you want to group the data.
- "StoreOpenings": The name of the new column in the summarized table.
- [StoreOpenings]: The measure or column that contains the values you want to summarize.
If Power BI is not accepting your code, it might be due to the [StoreOpenings] measure not being defined correctly. Ensure that you have a measure named [StoreOpenings] in your model. For example:
Define the Measure:
StoreOpenings = COUNTROWS(FILTER('YourTable', 'YourTable'[OpeningDate] = [SelectedMonth]))Use the Measure in SUMMARIZE:
StoreOpeningsTable = SUMMARIZE( 'YourTable', 'YourTable'[Month], "StoreOpenings", [StoreOpenings] )
If you don’t have a measure named [StoreOpenings], you need to create it first. Here’s a complete example:
Create the Measure:
StoreOpenings = CALCULATE( COUNTROWS('YourTable'), FILTER('YourTable', MONTH('YourTable'[OpeningDate]) = MONTH(TODAY())) )Create the Summarized Table:
StoreOpeningsTable = SUMMARIZE( 'YourTable', 'YourTable'[Month], "StoreOpenings", [StoreOpenings] )
Make sure that [StoreOpenings] is correctly defined and available in your model before using it in the SUMMARIZE function. If you still encounter issues, please share the specific error message you’re getting, and I’ll be happy to help further!