Forum Discussion
Leading zeros getting lost in dataflow
Somewhere these values are being changed to numbers and the leading zeros are dropping. They must be forced to be strings in the dataflow itself. If you let it detect the datatype when storing, it will use a numerical type.
I am sorry, I tried to exemplify using a simpler version of my DAX Code and did not pay attention to this (did not test in Power BI). I am aware of the need to use an iterator to generate a row context and therefore to use the RELATED function.
Here's the error I get using a DAX measure similar to what you suggested. Also the relationship is displayed:
The thing is, if I switch to Import Mode (or connect to a Datamart, for that matter), the measure suddenly works:
I opened a support ticket and for now the Microsoft employees who've kindly helped me made a suggestion for me to test if retrieving these tables from the same dataflow works (instead of getting it from different dataflows, which is the case in my model). It wouldn't be practical for me to have it in the same dataflow but I tested it for the sake of the hypothesis, and it hasn't worked either.
It really is delaying progress in my report so I would be really glad to solve it. Anyway thanks for the help you are already giving me.
As for the data source/data types issue, the data source is an IBM DB2 Database. The following are screenshots of the data types in each of the two tables of the example (it's in schema view):
d_ITEM (dimension table)
f_FECHAFIS (fact table)
Thank you.
I do believe the issue is it is in separate dataflows. For DAX to work, it must create a SQL query that will get sent back to the server and the server will compile the data and return the results. By being in two different dataflows, which are in effect two different servers a SQL statement cannot be generated, so it fails.
So either go import mode, or move these to the same dataflow. Direct Query has a lot of limitations. This is one of many. I avoid DQ models as a rule. I spend more time debugging this kind of thing than getting work done.
Alright. I will mark your first answer as the solution as it is related to the original question. Also changing the data type in the transformation is indeed what solved the original problem.
I guess I will just use Import Mode and try to streamline the refreshes with Power Automate instead. Also I'll continue working in the support ticket.
Thank you.
I don't believe it is because of separate dataflows.
- I did move all my tables to the same dataflow and tested it, the measure still does not work.
- Even in DirectQuery with separate dataflows, although the measure doesn’t work, when using the ITEM dimension table to filter the FECHAFIS fact table in the visualizations, the relationship works. Isn't the visualization effectively sending a SQL Query back to the server in this case too? In other words, it the issue was with separate dataflows, wouldn't the visualization filtering NOT work too?
No. On the DAX.Guide page there are notes that RELATED() isn't 100% functional with Direct Query tables, though it doesn't go into specifics, other than it needs a regular relationship vs a limited relationship.
I would advise you continue working with MS on the support ticket. You are beyond the normal use case here and may have found a bug or an undocumented limitation with RELATED(), and I assume RELATEDTABLE(), when using Direct Query against dataflows. I suspect this would work if your data was in a SQL Server, but cannot confirm.
Or, convert to import, as that is where Power BI and all of the DAX will work without limitation.