Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
What I'm trying to do:
1. Use DAX Summarize to group a table by two text columns like this: D_BatchID = SUMMARIZE(F_StockEvent, F_StockEvent[ItemID], F_StockEvent[BatchID])
2. Create a new column to that table by combining the two existing columns, separated by "¤", like this:
ItemID¤BatchID = D_BatchID[ItemID]&"¤"&D_BatchID[BatchID]
3. Use this column to create one-to-many relationship.
The problem is that when I use Summarize, the resultant table has some rows that look equal but are treated as different. After I combine the columns to form a new column, the resultant rows are actually equal and therefore cannot be used to create one-to-many relationship. See the images for clarification:
As can be seen from the screenshots above, the BatchID column has 5 distinct values but when combined with the ItemID column (that has only one distinct value), the resultant ItemID¤BatchID-column only has 4 distinct values. I also created a new column for debugging purposes: Column = LEN(BatchID[BatchID]). This returns blank for one of the rows and 0 for the other, which I suppose means that the first one is a blank string and the other one is an empty string, which are not the same thing, but when combined with another string the result for both is the same? The question is: how can I avoid this? I want a table that only has one row for each ItemID-BatchID combination, and for my purposes blank and empty are the same thing.
Solved! Go to Solution.
Hey @v-piga-msft,
Thanks for the help. I played around with the query editor and managed to fix the problem using Table.ReplaceValue, replacing null values with empty values (Table.ReplaceValue(F_StockEvent_dummy_table,null,"",Replacer.ReplaceValue,{"BatchID"})). There was no space character in the BatchID, it was just empty while other rows had null.
Hi @Anonymous,
This returns blank for one of the rows and 0 for the other, which I suppose means that the first one is a blank string and the other one is an empty string, which are not the same thing, but when combined with another string the result for both is the same? The question is: how can I avoid this? I want a table that only has one row for each ItemID-BatchID combination, and for my purposes blank and empty are the same thing.
I have made a test to reproduce your scenario but I cannot reproduce it.
If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Hi @Anonymous,
By my tests, it may caused by your data source.
When I copy your table to Excel and get data with Excel, create the calculated table with SUMMARIZE. It show like below.
If I confirm each blank value in BatchID is null. It will show like below.
It seems that you may have a space in BatchID, so you will get the issue.
Best Regards,
Cherry
Hey @v-piga-msft,
Thanks for the help. I played around with the query editor and managed to fix the problem using Table.ReplaceValue, replacing null values with empty values (Table.ReplaceValue(F_StockEvent_dummy_table,null,"",Replacer.ReplaceValue,{"BatchID"})). There was no space character in the BatchID, it was just empty while other rows had null.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |