Help with Relationships and Merging Queries !

[alisonpappas]

Hi Everyone!

Hope someone is able to provide some assistance. I am going to try to make this as simple as possible but if more detail is needed let me know.

I have 2 types of data, let's call them Source A and Source B. Source Source A we can *say* filter the products at a family level, product level, and product level 2. Source B can only go to family level and product level 2. When I try to compare the 2 types of data the data unaccounted for (product level 2) then just returns the same number. See below.(676905)

I would like to know if there is a way so when I filter it these aren't included with the filters/returns a 0 or a N/A type of thing? I would really like a non-dax fix, because there are a lot more levels than I explained. 

[mahoneypat]

If you are looking for a power query fix, you will need to provide examples of both tables (maybe with >2 levels if that is your situation), so the community can help work through.  

Also, can you show more than those 2 columns on your desired ouput?  Can't tell what you are analyzing over (Family, Product, Level 2, etc.).

Regards,

Pat

[alisonpappas]

Hi Pat,

Note: Source A and B are 2 different data sources. 

Source A is the activity and B is the plan. The Plan can get split into Level 1, 2, and 3. Level 1 is shown and is from Source A and notice how there is a "blank" and then the next one has a name next to it. That is because in Level 1 both A and B can go to the crossed out level and have item matching that but then the rest is un accounted for. 

So when I filter to level 2 and use Source B data source I am able to filter down to a deeper level, however Source A is unable to split up into this level.

So this 8810 is full of products that do not fit into any category. When the total is there it is fine because i want to know all products at all levels. However when I filter these out they are double counted for. I want to know how to not include Source A but still include Source B because the 8810 techincally doesn't belong into either parts once the new level is created.

[mahoneypat]

I read through your update a few times, but still struggling to understand your scenario.  You said A and B are two data sources, but have you kept them in two separate tables?  Merged them together? Is there a relationship between them?  Family or Level 1?  I expect the solution will be a relatively simple DAX solution once that is clear.  Can you post the diagram view from your model that shows tables, fields and relationships?

Regards,

Pat

[alisonpappas]

Hi Pat,

You'll see that I have the two seperte tables and how they match up. The left over amount that does not have a match gets the same amount however if I drilled how into a deeper level you can see that they then have the ability to split.

I want to be able to have the split work even when the third layer isn't added in. I want to be able to filter by other levels and then have the break outs happen and not be duplicates but it only happens sometimes. Does this make sense? 

[mahoneypat]

Sorry if I am being dense, but still not clear (at least to me).  You added two pictures of tables but those are table visuals.  I was hoping you would show the tables in your data model (on the Model View tab) and the relationships between them.  To propose a DAX expression for the functionality you are looking for that information is needed.  Can you also share the relevant measure(s) that you have created so far that you have used in the table visuals shown (e.g., Activity and Plan are measures?)

Regards,

Pat

[alisonpappas]

Hi Pat,

Here are the relationships. The activity and plan are just the total so for each product it can be put into multiple parts and multiple geographys and the activity and plan are just the count of them. No special dax.

[mahoneypat]

Sorry for the delay.  Thank you for providing the model.  I think the functionality you are looking for will be challening for a couple reasons - 1. your model is pretty complex and has multiple bi-directional relationships and 2. you are looking to use fields in your measure that are not in your visual.  Having said that, your pics of the table visual looks like you are using two "GTM" columns, one from plan and one from activity.  I think the key to what you are looking for will leverage the TREATAS() function to pass the values of GTM in one table to the GTM column in the other.  Are the values the same in both columns?

If so, you can try this type of a measure pattern:

NewMeasure = Calculate([YourMeasure], TREATAS(Values(Plan[GTM]), Activity[GTM])) // or vice versa for Activity/Plan

The column in the VALUES() would be the one in the visual and can be used to filter the GTM column not included in the visual.  Please let me know if this gets you any closer to your goal.

Regards,

Pat

[alisonpappas]

Hi Pat!

Thanks for your help!

You can see above how the files have been able to affect the quieries made.  I know the data is complex I can't merge them any farther Power BI runs out of data when I try and then still with the relationships they have to have no duplicates which is hard.

The DAX did work however I am wonderng if this may be a temporary fix? For example, now at the GTM level the total that does not match up to the next level, it is ignored which is what I wanted to see. However if I wanted to filter at a high level where it is included would I need to have a new DAX? Like because the ignored values I would want to come back in. Does this make sense?