Measure names display as field values under a new column

[sureshg2498]

Hi All,

We have a requirement to change the data structure where we need to display measure names as field values as shown below.

Is there any DAX code to create a new table which displays the required data structure. Could anyone please help me on this?

Thanks,

Suresh.

@Bibiano_Geraldo @rajendraongole1 @Ritaf1983 @danextian  @lbendlin @Ritaf1983 @bhanu_gautam

[v-csrikanth]

Hi @sureshg2498 

Thank you for being part of the Microsoft Fabric Community.

As highlighted by @danextian @GrowthNatives @Vijay_Chethan, the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolve
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[sureshg2498]

Thank you for your response Srikanth.

[danextian · Accepted solution]

Hi @sureshg2498 

Assuming the name of the original table is Budget, try  this:

Budget2 = 
VAR _Country = 
    DISTINCT ( Budget[Country] )  -- Get unique list of countries from the Budget table

VAR _Metric = 
    SELECTCOLUMNS ( { "Spent Budget", "Remaining Budget" }, "Metric Name", [Value] )  -- Create a table with metric names

VAR _CrossJoined = 
    CROSSJOIN ( _Country, _Metric )  -- Cross join countries with the two metric names to form all combinations

RETURN 
    ADDCOLUMNS ( 
        _CrossJoined,  -- Add a calculated column to the cross-joined table
        "Value", 
            VAR _country = [Country]  -- Current row context country
            VAR _tbl = 
                FILTER ( Budget, Budget[Country] = _country )  -- Filter Budget table for current country
            RETURN 
                SWITCH ( 
                    [Metric Name],  -- Choose logic based on metric name
                    "Spent Budget", SUMX ( _tbl, [Spent Budget] ),  -- Sum Spent Budget for the country
                    "Remaining Budget", SUMX ( _tbl, [Remaining Budget] )  -- Sum Remaining Budget for the country
                )
    )

[sureshg2498]

Hi Danextian - I just wanted to thank you for taking the time to answer my question. Your explanation was really clear and helpful, and it gave me exactly what I needed to move forward.

 I truly appreciate the time you took to answer all of my questions and address my concerns. Thank you! 

[GrowthNatives]

Hi @sureshg2498 , 
i understand you are having issue with displaying measure names as field values under a new column. How I would go about is below:

🧮 DAX to Create the New Table

UnpivotedBudget =
UNION(
    SELECTCOLUMNS(
        BudgetTable,                   -- Replace with your actual table name
        "Country", BudgetTable[Country],
        "Metric Name", "Spent Budget",
        "Value", BudgetTable[Spent Budget]
    ),
    SELECTCOLUMNS(
        BudgetTable,
        "Country", BudgetTable[Country],
        "Metric Name", "Remaining Budget",
        "Value", BudgetTable[Remaining Budget]
    )
)

📘 Explanation

• SELECTCOLUMNS() is used to define the new column names explicitly.

• UNION() stacks the two versions of the table — one for Spent Budget, one for Remaining Budget.

• Each row from the original table becomes two rows in the new table (one for each metric).

• This is called a manual unpivot using DAX.

✅ Result

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore more]

[Vijay_Chethan]

Hello sureshg2498,
you could achieve your use case using matrix visual, using following settings:
layout ---> Tabular
values --->(Options)--->switch values to rows

if this helps, please mark as solution

[sureshg2498]

Thank you for your response, Vijay.

Actually we need a seperate table in Power BI desktop with columns Country, Metric Name, Value.