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 actually don't understand that error either as you shouldn't have been able to enter a measure like that. You entered a raw field - f_fact[Quantity Sold]. You cannot do that in a measure and the proper error would be along the lines of unable to determine a single value for that field.
You need to use an iterator. Try this:
SUMX(
f_fact,
f_fact[Quantity Sold] * RELATED(d_Item[List Price])
)
The iterator generates row context and RELATED() understand that, and can combine that with the model's filter context to get the correct values. Without the iterator, there was no row context, only filter context. And RELATED had no clue what to do.
As to the data type issue you never shared your M code and I don't know what the data source is. Not all data sources explicitly type their data or convey that info to Power Query if they do. SQL Server does, CSV files do not. There are 100+ different sources and some do, most don't. You never shared screenshots of the data either. Was the data type set as ABC or was it ABC/123. If the latter, then Power BI didn't know what it was and left it as the "Any" type, which IMHO should be an error and not allowed. Always explicitly type your data. Every single column. Never leave it up to Power BI to guess.
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.