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

[Anonymous]

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,

[tamerj1 · Accepted solution]

@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

[tamerj1]

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. 

 

[Anonymous]

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?

[tamerj1]

@Anonymous 
Please try

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

[Anonymous]

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.

[tamerj1]

@Anonymous 

Please use your own table and column names

[Anonymous]

@tamerj1 

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

[tamerj1 · Accepted solution]

@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]

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

[tamerj1 · Accepted solution]

@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]

@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?

[tamerj1]

@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]

@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?

[tamerj1]

@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]

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

[tamerj1]

@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]

@tamerj1 Thank you so much! It worked.