Merged Column in PowerQuery - Data disappears when selecting visual filters in report

[MrNew]

Hi

I haven't been able to find a similar problem to this online so here goes:

I've merged two data tables - my Product table, and my Jobs Table. Common column being ProductCode. The jobs table has all the data I need for my visual except the product cost, which is why I merged the two (Seperate issue -  For the life of me I cannot figure out how I can retrieve that information at a row level via an inactive relationship, CALCULATE, MAX & SUM combined with USERELATIONSHIP just return max of a single product cost and sum of all costs even if my visual has every row displayed).

So here is my Jobs table visual after i've merged with the merged column showing values (standardcost)

If I then click on a another visual in the report that filters by location, the product details are filtered and remain but the cost value disappears?? 

How is this possible, if the cost has been MERGED as a new column in powerquery? Every row now has a value in the Jobs table - all that has happened is the visual has been filtered, why does the values of a MERGED column disappear?

Thanks.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Clearly show the problem there.

[Anonymous]

How have you performed the data merge? If you have done this through transformation you should not be losing data when filtering (as the data will technically exist in the table).

If you have selected data from 2 tables using a Tabular visual, it will filter based on relationships as the data is not located within the table and PowerBI may not be able to determine the relationship between records.

[MrNew]

Yeah I've performed the merge via transforming data in powerquery. I've updated powerBI and the issue still persists. 

[Anonymous]

if you're losing data during transformation I'd recommend you look at the merge step and ensure you're doing a left outer join.

You should not be losing any data when doing this. If you are, then it may mean you have multiple options and it cannot return a single value for you, so it might be returning the first it finds.

[bolfri]

I think you should reconsider using only one fact table. What was the issue then? Can you share sample data with only few rows so I can help you with that?

[MrNew]

Hi, thanks for taking the time to respond. The issue is that when the Jobs table  (Pending List Details - see screen shots) is filtered by another visual, the merged column "StandardCost" values disappear. This doesn't make any sense, since the DateAdded, DaysInList and sickbayproduct columns are all filtered correctly, and the StandardCost has been merged in powerquery, so is ACTUAL data in the table, just like DateAdded, DaysInList and sickbayproduct. 

[bolfri]

Did you checked after merging if for this product there is a corresponding Standard Cost Value in your data?

[MrNew]

Yup, they all have a standard cost 😕

[bolfri]

Can you share a PBIX file with few sample records to actually see what's goin on?