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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bw70316
Helper V
Helper V

Dax Works in One Table Not Other / Is my Model Looking Correct?

First I want to tackle the issue as to why one table is accepting a DAX formula while the other isn't despite all the qualifiers being essntially the same (date marked as date in model, FY is text, etc.). 

This DAX works to create a FY column in my Program Dollars Table: 

 

Fiscal Year = CONCATENATE("FY", IF(MONTH('Program_Dollars'[Last_Updated]) <=6, Value(Format('Program_Dollars'[Last_Updated],"YY")), VALUE(FORMAT('Program_Dollars'[Last_Updated],"YY"))+1))
 
Here is the DAX that is NOT WORKING despite the only thing I changed is the table name:
 
Fiscal Year = CONCATENATE("FY", IF(MONTH('Income'[Last_Updated]) <=6,
VALUE(FORMAT('Income'[Last_Updated],"YY")), VALUE(FORMAT('Income'[Last_Updated],"YY")) +1))

 

The error I get is: "Cannot convert value " of Text to Type Number"

 

The whole reason I am doing this is because my Calendar table does not seem to have a relationship with Program Dollars. When I try to use the Calendar as a filter for Viz that include Program Dollars, the visuals become blank. I had a colleague build this model for me since he was more familiar with the databases involved. If you can think of a way to improve my model please let me know:

 

 

Capture.PNG

 

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@bw70316 
Value(Format('Program_Dollars'[Last_Updated],"YY")

You use format() turn the [Last_updated] column into text and then use value() to convert it to numbers. The problem is you cannot convert it in the Income table.

 

I wonder what are the data type for the [Last_updated] columns in the two tables, are they the same?


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bw70316
Helper V
Helper V

I removed the blanks from the income table last updated column and the FY worked.

 

If anybody is willing to take on the challenge of my model so that I can filter through income and program dollars based on the calendar FY (If possible with the way the data is structured) I would greatly appreciate it. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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