Assistance with DAX - Syntax and Compatibility errors

[bmoh]

Hi There!

I'm trying to figure out how to link two columns from two tables compatible.

The current relationship in model view exists as follows:

I have created two DAX expressions in both tables to restandardize the format. 

Standardized_Quarter =

VAR YearNum = RIGHT(LEFT(Dataset[Quarter], 4), 2)

VAR QuarterPart = MID(Dataset[Quarter], 6, 2)

RETURN "FY" & YearNum & QuarterPart

Standardized_Fiscal_year_quarter =

VAR YearNum = RIGHT(LEFT(time_dim[Fiscal Year], 4), 2)

RETURN "FY" & YearNum & " Q" & time_dim[Fiscal Quarter]

I'm getting a syntax error in the first expression and an incompatibility error in the second. I'm relatively new to building DAX in Power BI. If you are familiar with how to resolve this or could direct me to someone who could that would be greatly appreciated. 

Precisely, the 2nd error states: 
"A single value for column 'Fiscal Year' in table 'time_dim' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Thank you in advance for your assistance!

[bmoh · Accepted solution]

I figured out the problem. I created new measures within the table; whereas, the dax functions should have been written as new colum. The problem has been resolved now. Thank you

[Anonymous]

Hi, @bmoh 

I’m delighted to hear about your progress! It sounds like a very clever solution, and I appreciate that you’ve already shared most of it, which is incredibly helpful to us.

If possible, I would love for you to share the modified DAX as well. Additionally, please click the icon shown in the image below to mark your response as the accepted solution:

This is vital for the growth of our community, as it will enable other members facing similar issues to find solutions more quickly.

Thank you in advance for your contributions. Wishing you a wonderful week ahead!

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

[bmoh]

Hi Leroy,

I used the exact same DAX as above just under "New Column" option rather than "New Measure". Thank you