topic Re: Hello All, I need help on how to use DAX expression in Power BI Desktop in DAX Commands and Tips

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

Anonymous — Fri, 17 Feb 2023 14:06:12 GMT

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. Please let me know how I would go about this. Any help would be much appreciated!!

Thank you all,

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

tamerj1 — Fri, 17 Feb 2023 14:16:47 GMT

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.

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

Anonymous — Fri, 17 Feb 2023 14:27:37 GMT

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?

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

tamerj1 — Fri, 17 Feb 2023 15:07:26 GMT

Anonymous
Please try

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

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

Anonymous — Fri, 17 Feb 2023 15:32:18 GMT

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.

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

tamerj1 — Fri, 17 Feb 2023 16:04:49 GMT

Anonymous

Please use your own table and column names

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

Anonymous — Fri, 17 Feb 2023 18:31:43 GMT

tamerj1

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

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

tamerj1 — Sat, 18 Feb 2023 00:33:44 GMT

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

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

Anonymous — Tue, 21 Feb 2023 13:55:08 GMT

tamerj1 Thank you so much! It worked.

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

Anonymous — Tue, 21 Feb 2023 14:02:13 GMT

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

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

tamerj1 — Tue, 21 Feb 2023 14:08:46 GMT

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 ) )

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

Anonymous — Wed, 22 Feb 2023 21:05:27 GMT

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?

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

tamerj1 — Thu, 23 Feb 2023 02:27:38 GMT

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.

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

Anonymous — Thu, 23 Feb 2023 15:05:12 GMT

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?

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

tamerj1 — Thu, 23 Feb 2023 15:11:22 GMT

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.

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

Anonymous — Thu, 23 Feb 2023 15:23:34 GMT

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

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

tamerj1 — Thu, 23 Feb 2023 15:35:05 GMT

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)