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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Hello All, I need help on how to use DAX expression in Power BI Desktop

Hello all,

 

When I make a Table style report using the power bi dataset, I occasionally need to add another column and use a DAX expression to give me a calculated value. For example, I may add two columns: "Employee ID" and "Fiscal Year" but I need to add another column next to it and conacatenate both employee ID and Fiscal Year to give me a unique identifying number. I have attached a screenshot of the report and the screenshot shows how Power BI doesn't let me add another column or let me use DAX expression with fields I want. Capture.JPGPlease let me know how I would go about this. Any help would be much appreciated!!

 

Thank you all,

2 ACCEPTED SOLUTIONS

@Anonymous 

I mean in the code

instead of 'Table' use the real table name of the table containing the column 

same for the column names

View solution in original post

@Anonymous 
Why?

You can use CONCATENATE instead of & like

=
CONCATENATE (
    CONCATENATE ( MAX ( 'Table'[employee ID] ), " - " ),
    MAX ( 'Table'[Fiscal Year] )
)

If you have multiple values per row then use

=
CONCATENATEX (
    'Table',
    'Table'[employee ID] & " - " & 'Table'[Fiscal Year],
    UNICHAR ( 10 )
)

View solution in original post

16 REPLIES 16
tamerj1
Super User
Super User

Hi @Anonymous 

Apparently this is a live connection. You are corrected to Power Bi dataset through PowerBi services. You cannot edit the dataset by adding calculated columns. You can only add measures. As you can see even the table view is not available. 

BC26D82A-60F7-474E-86B6-221831AA3C50.jpeg

Anonymous
Not applicable

Hi tamerj,

 

Thank you so much for your quick response but is there any work around it? Even when I try to add a new measure and conactenate the "employee ID" and "Fiscal Year". The formula bar does not drop down the employee ID or the Fiscal year field. Is there any way to concatenate these two fields?

 

@Anonymous 
Please try

=
MAX ( 'Table'[employee ID] ) & " - "
    & MAX ( 'Table'[Fiscal Year] )
Anonymous
Not applicable

Hello tamerj,

 

When I type in the formula it says that it is incorrect and does not give me any output. Please see screenshot  below and let me know if I did anything wrong here.image.png

@Anonymous 

Please use your own table and column names

Anonymous
Not applicable

@tamerj1 

 

I changed my column names like you mentioned but it still does not seem to work. Am I doing something wrong here?image.png

@Anonymous 

I mean in the code

instead of 'Table' use the real table name of the table containing the column 

same for the column names

Anonymous
Not applicable

@tamerj1 Is there any way to use the "CONCATENATE" formula instead of "MAX"?

@Anonymous 
Why?

You can use CONCATENATE instead of & like

=
CONCATENATE (
    CONCATENATE ( MAX ( 'Table'[employee ID] ), " - " ),
    MAX ( 'Table'[Fiscal Year] )
)

If you have multiple values per row then use

=
CONCATENATEX (
    'Table',
    'Table'[employee ID] & " - " & 'Table'[Fiscal Year],
    UNICHAR ( 10 )
)
Anonymous
Not applicable

@tamerj1 Why do we have to use "MAX" to pull the data? I know I have a live connection to Power BI desktop but I thought "MAX" was used to return the largest value in a dataset. Can you please clarify?

@Anonymous 

That is true when used in a calculated column but when used in a measure then MAX returns the maximum value available in the current filter context which in many cases happens to be a single value specially when the visual works on row level. 

Anonymous
Not applicable

@tamerj1 That makes sense but why does it only let me access the tables when I use 'MAX' function? Also, you gave me a formula for if the row had multiple values then use =

CONCATENATEX (
    'Table',
    'Table'[employee ID] & " - " & 'Table'[Fiscal Year],
    UNICHAR ( 10 )
)

What is unichar(10)? do I have to put the number 10 in there evrytime?

@Anonymous 
CONCATENATEX is and iterator. Iterators provides access to the row context of the table under iteration ('Table' in this case). Therefore, inside the iterator function you can reference directly any column name of the iterated table as it has only one value. Other than that you need to provide an aggregation of the column inside the function when creating measures.

Anonymous
Not applicable

@tamerj1 what is unichar(10)? Do I have to put the number 10 inside the brackets everytime?

 

@Anonymous 

UNICHAR ( ) is a function that returns the special character indicated by unicode inside the function. 10 is the unicode of the skip line (new line). Incase the rows in the table visual are aggregated the the values of the aggregated rows will appear separated by new lines (on top of each other)

Anonymous
Not applicable

@tamerj1 Thank you so much! It worked.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.