Leading zeros getting lost in dataflow

[brunoctesser]

I am developing a Power BI Desktop report connected to a dataflow via DirectQuery. One of my dimension tables has a column (Item) with the following values (greatly reduced for demonstration purposes):

Item (String type)	Description (String type)
000010500	Sheet Metal Plate 3mm
000010600	Sheet Metal Plate 4.75mm
10500	Hammer
10600	Nitrogen Gas
000011211	Keyboard
000011212	Monitor

The items 000010500 and 10500 are completely different items, but Power BI does not let me set this as a unique key column for the one-side of a relationship because it seems to be ignoring the leading zeros in one of the items, and therefore assuming there are duplicate values.

I noticed this does not happen when connecting to a datamart with the exact same table, or when connecting to the dataflow via import mode. I suspect this is because the dataflow store the tables in .csv files.

Would there be a work around for this problem? Thanks in advance.

[Alef_Ricardo_]

It seems that you are experiencing an issue with Power BI Desktop when using DirectQuery to connect to a dataflow. The issue appears to be that Power BI is ignoring the leading zeros in one of your dimension table columns, which is causing it to assume that there are duplicate values.

Unfortunately, I couldn't find any specific information about this issue in my search results. However, one possible workaround could be to create a calculated column in your dimension table that concatenates a prefix (such as "Item") to the Item column values. This would ensure that the values are treated as unique strings, even if they have leading zeros.

For example, you could create a calculated column with the following formula:

```
Item Key = "Item" & [Item]
```

This would create a new column with values such as "Item000010500" and "Item10500", which should be treated as unique by Power BI. You could then use this new column as the key column for your relationship.

I hope this helps! Let me know if you have any further questions or if there's anything else I can assist with. 😊

(1) Need a leading zero on a Month with DirectQuery. https://community.fabric.microsoft.com/t5/Desktop/Need-a-leading-zero-on-a-Month-with-DirectQuery/m-....
(2) Add Leading Zeros to a Number in Power BI Using Power Query. https://radacad.com/add-leading-zeros-to-a-number-in-power-bi-using-power-query.
(3) Usar o DirectQuery no Power BI Desktop - Power BI | Microsoft Learn. https://learn.microsoft.com/pt-br/power-bi/connect-data/desktop-use-directquery.
(4) Diretrizes sobre modelos de DirectQuery no Power BI Desktop. https://learn.microsoft.com/pt-br/power-bi/guidance/directquery-model-guidance.

[edhans · Accepted solution]

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.

[brunoctesser]

Thanks for the answer. I understand that, but besides defining the data type as text in the dataflow (which I already did) what other steps are possible?

[edhans]

You will need to show me the M code for the query. If it isn't working, then something else is going on in the code. The dataflow isn't changing the datatypes without being told to.

[brunoctesser]

It seems to have worked when I explicitly set the data type to text in the query, even though it came originally as text from the data source before. Now I am able to define this as the one-side of a relationship. It's doesn't make much sense and maybe I am missing something, but it worked.

However, let's say I need to refer to this dimension table to get the list price for an item and multiply it by the quantity sold that resides in a fact table. I would use something like this:

Measure = f_fact[Quantity Sold] * RELATED(d_Item[List Price])

I am not being able to do this. Power BI returns the error "The column 'd_Item[List Price]' either doesn't exist or doesn't have a relationship to any table available in the current context."

The column does exist and the tables have a valid relationship. Also, I looked it up and RELATED is supported by Direct Query. The relationship seems to work when building visualizations in the report (d_Item filters f_fact), but somehow when running the DAX query the relationship is invalid.

I really don't know what's going on.

[edhans]

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.

[brunoctesser]

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.

[edhans]

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.

[brunoctesser]

I don't believe it is because of separate dataflows.

1. I did move all my tables to the same dataflow and tested it, the measure still does not work.
2. 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?

[edhans]

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.

[brunoctesser]

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.