Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Kris48
Frequent Visitor

create two calculated columns (Concatenatex)

Please help me create two calculated columns. They can be created in Power Query (I don't care). I would like to create new "Module_Ver 2" and "Value_Ver 2" speakers. I have explained the problem in the pictures below
Link PBIX: https://drive.google.com/file/d/1lebnBAOhJITh1b3DwQjT221upIkNVaDL/view?usp=sharing 

 

3.png

 4.png

 

1.png

2.png

1 ACCEPTED SOLUTION
tharunkumarRTK
Super User
Super User

@Kris48 

Module Ver 2

Screenshot 2024-11-17 at 12.41.15 PM.png

Value Ver 2
Screenshot 2024-11-17 at 1.03.12 PM.png

 

please fine the PBIX attached

 

Need a Power BI Consultation? Hire me on Upwork

 

 

 

Connect on LinkedIn

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!


PBI_SuperUser_Rank@2x.png

View solution in original post

3 REPLIES 3
tharunkumarRTK
Super User
Super User

@Kris48 

Module Ver 2

Screenshot 2024-11-17 at 12.41.15 PM.png

Value Ver 2
Screenshot 2024-11-17 at 1.03.12 PM.png

 

please fine the PBIX attached

 

Need a Power BI Consultation? Hire me on Upwork

 

 

 

Connect on LinkedIn

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!


PBI_SuperUser_Rank@2x.png
Kris48
Frequent Visitor

Thanks for the reply. Previously, I also tried to use ChatGPT - gpt4, unfortunately it could not help me as in this case

 

Adding column Value_Ver 2 comes out nonsense:
5.png

 

The expected result is as follows:
2.png

VahidDM
Super User
Super User

Hi @Kris48 

 

To create the two calculated columns "Module_Ver 2" and "Value_Ver 2" in Power BI, you can use the CONCATENATEX function in DAX. Here's how you can do it:

Assumptions:

  • Table Name: Replace 'YourTable' with the actual name of your table.
  • Grouping Column: Replace 'KeyColumn' with the column you want to group by (e.g., an ID or date).
  • Columns to Concatenate: 'Module' and 'Value' are the columns you want to concatenate.

Steps:

1. Module_Ver 2 Column

Create a new calculated column in your table with the following DAX formula:

Module_Ver 2 = 
VAR CurrentKey = 'YourTable'[KeyColumn]
RETURN
CONCATENATEX(
    FILTER(
        'YourTable',
        'YourTable'[KeyColumn] = CurrentKey
    ),
    'YourTable'[Module],
    ", "
)

2. Value_Ver 2 Column

Create another calculated column with this formula:

 

Value_Ver 2 = 
VAR CurrentKey = 'YourTable'[KeyColumn]
RETURN
CONCATENATEX(
    FILTER(
        'YourTable',
        'YourTable'[KeyColumn] = CurrentKey
    ),
    'YourTable'[Value],
    ", "
)

 

Explanation:

  • CurrentKey: Captures the value of the grouping key for the current row.
  • FILTER: Filters the table to include only rows with the same key as the current row.
  • CONCATENATEX: Concatenates the specified column's values (e.g., 'Module' or 'Value') from the filtered table, separated by ", ".

Alternative Using Power Query:

If you prefer to do this in Power Query:

  1. Group By Key Column:

    • Go to Home > Group By.
    • Group by your key column (KeyColumn).
    • Create two aggregation columns:
      • Module_Ver 2: Use the operation All Rows.
      • Value_Ver 2: Use the operation All Rows.
  2. Add Custom Columns:

    • Add a custom column for Module_Ver 2:

= Text.Combine([GroupedRows][Module], ", ")

 

Add a custom column for Value_Ver 2:

= Text.Combine([GroupedRows][Value], ", ")

 

  1. Cleanup:

    • Remove unnecessary columns resulting from the grouping.
    • Ensure only the desired columns remain.

Note:

  • Replace 'YourTable', 'KeyColumn', 'Module', and 'Value' with the actual names from your dataset.
  • Ensure that your data model relationships support the calculations.

By following these steps, you should be able to create the two new columns as per your requirements.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

 

LinkedIn|Twitter|Blog |YouTube 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.