Many to Many filter table problem  [Solved]

Anything related to PowerPivot and DAX Formuale
jefferson airplane
Posts: 6
Joined: Wed Jul 25, 2018 7:05 pm

Many to Many filter table problem

Postby jefferson airplane » Wed Jul 25, 2018 7:10 pm

Hello,

I have a M2M model with a bridge table. But I have a show stopper I am struggling to overcome. I have attached a jpeg with all details.

Can someone please help me?

Tried to explain as best I can but if any queries just ask. Thanks in advance.
Attachments
M2M problem.jpg
M2M problem.jpg (45.76 KiB) Viewed 827 times

MattAllington
Posts: 962
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Many to Many filter table problem

Postby MattAllington » Thu Jul 26, 2018 7:06 am

I suspect the image you have created is not accurate (at a guess). Both relationships state "many to one". But is the relationship between Table A and bridge looks like it should be "one to many". Otherwise I don't think it can be classed as a bridge table.

If Table A and Table be are dimension/lookup tables (unique key)
and If the bridge table is a data table and is always on the many side of the 2 relationships, then

...your measures that use the bridge table as the input will pass the filter from one of the lookup tables to the other - that's what it does.

If that doesn't answer your question, then can you perhaps show some real data illustrating the problem, making it clear when table everything comes from and the correct direction of the relationships.

Also, you may like to read this blog article here https://exceleratorbi.com.au/many-many- ... explained/
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
[URL=https://exceleratorbi.com.au/power-bi-online-training/]Power BI Online Training[/URL]

jefferson airplane
Posts: 6
Joined: Wed Jul 25, 2018 7:05 pm

Re: Many to Many filter table problem

Postby jefferson airplane » Thu Jul 26, 2018 6:57 pm

Matt, firstly thank you for your message.

You are right, it is just a typo in the picture. Table A/B are related to Bridge Table by "one to many" - I have attached the real data model.

I have not read through the article you sent to me yet. However, I'll try to clarify better...

When you build a measure, you should specify the "Table Name". This is in a way my issue...

However, for my purposes, "Table Name" has to be Table A. The measure I need has to calculate a sum of a value in Table A filtering against values from a column in Table B.

E.g.

when creates a measure assuming Table Name = OP2019... (Table A)

I need:

Sum of (Total Heads Plan) in Table A has to be filtered against (Confidence) in Table B

Confidence is a column contains six unique values as text. Filter above has to be set with three values out of six.

=Calculate(Sum(Total Heads Plan), Bridge_Table) is what I am using so far but this allows to filter using only columns from Table A (given it is specified as Table Name).

Is there any formula/strategy can work around this? or I can assume it is a limitation?

Hope now clarified. If any queries let me know. Thanks a lot
Attachments
2018-07-26_093124.png
2018-07-26_093124.png (21.09 KiB) Viewed 795 times

Oxenskiold
Posts: 35
Joined: Tue Jan 05, 2016 10:38 pm

Re: Many to Many filter table problem

Postby Oxenskiold » Thu Jul 26, 2018 7:53 pm

Hi Jefferson,

this may sound silly but are you absolutely sure that the primary keys of table B (Proj_def_TV_OP18) exist as foreign keys in the bridge table and the same for Table A.

The measure you show should work under the conditions you specify.

You can try this measure in DAX studio or whatever other query interface you usually use. You'll have to insert the column and table names from your model.

Code: Select all

EVALUATE
SELECTCOLUMNS (
    Proj_def_TV_OP18,
    "PrimaryKey_Proj_def_TV_OP18", Proj_def_TV_OP18[primarykeyname],
    "ForeignKeyCount", COUNTROWS ( RELATEDTABLE ( bridge_table ) )
)


and the same for your table A. If the 'ForeignKeyCount' extention column mainly has a value of zero you might have a problem with the data import or your releationship setup.

This is just to check that the conditions for the filtering are in place.

jefferson airplane
Posts: 6
Joined: Wed Jul 25, 2018 7:05 pm

Re: Many to Many filter table problem

Postby jefferson airplane » Thu Jul 26, 2018 10:52 pm

Hi Oxenskiold,

not sure what behind your proposed evaluation as not really an expert.

By the way, I have tried but I am getting an error. Can you please advise? Thanks

2018-07-26_134726.png
2018-07-26_134726.png (24.63 KiB) Viewed 788 times


2018-07-26_134823.png
2018-07-26_134823.png (19.44 KiB) Viewed 788 times

MattAllington
Posts: 962
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Many to Many filter table problem

Postby MattAllington » Fri Jul 27, 2018 12:21 am

You can’t use that formula as a measure - it is a table formula and won’t work the way you have applied it. You can read my article here for more information. https://exceleratorbi.com.au/getting-st ... ax-studio/

The approach you have described will give a correct answer, but it sounds like the answer you are getting is not what you expect. I am guessing you perhaps have the wrong approach, but I can’t be sure. The names of the columns in your bridge table make me wonder.

=Calculate(Sum(Total Heads Plan), Bridge_Table) is what I am using so far but this allows to filter using only columns from Table A (given it is specified as Table Name).


The above is the correct formula but your description of how it works is wrong.

You need to
1 create a pivot table
2 place Confidence from table b on rows on the pivot
3 place the above measure in values
Then it will give the correct answers with table b filtering table a as a result of the bridge table being specified as the filter.

If it doesn’t give you what you expect, then either
1 your bridge table doesn’t have all the combinations
2 this approach is not what you need and you need something else.
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
[URL=https://exceleratorbi.com.au/power-bi-online-training/]Power BI Online Training[/URL]

jefferson airplane
Posts: 6
Joined: Wed Jul 25, 2018 7:05 pm

Re: Many to Many filter table problem

Postby jefferson airplane » Fri Jul 27, 2018 8:33 pm

Hello Matt

I have read your article; great stuff. So this now makes more confused because I cannot really understand why excel is still giving me the warning message when the model seems OK. I can guarantee Bridge Table is consistent and all combinations are completed.

So, I have built again the pivot table as you recommend and values are being shown correctly; makes me wonder the model is working fine but to the other hand excel is giving me the yellow message below.

Back again to the initial situation. The funniest thing is that I build exactly the same pivot table but specifying in both measures "Table B" instead of "Table A" everything goes smoothly - remember that "Confidence" is a column from "Table B".

Any thoughts?

Thanks again guys, much appreciated your support

2018-07-27_113158.png
2018-07-27_113158.png (12.71 KiB) Viewed 747 times

op hc trial.png
op hc trial.png (21.17 KiB) Viewed 747 times

op forec trial.png
op forec trial.png (21.43 KiB) Viewed 747 times

MattAllington
Posts: 962
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Many to Many filter table problem

Postby MattAllington » Tue Jul 31, 2018 10:03 am

The yellow warning message in Excel is likely a false warning. Unfortunately it cannot be turned off.

The principle of expanded tables means the following.

Place a filter on table A (eg a slicer or place a column in a row in a visual).
Write a measure for Table B as follows (place it in table B)
measure 1 = CALCULATE([some measure using data in table B],Bridge)

The filters on Table A will correctly filter Table B based on the matching records in Bridge.

The reverse is also true.
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
[URL=https://exceleratorbi.com.au/power-bi-online-training/]Power BI Online Training[/URL]

Oxenskiold
Posts: 35
Joined: Tue Jan 05, 2016 10:38 pm

Re: Many to Many filter table problem  [Solved]

Postby Oxenskiold » Tue Jul 31, 2018 8:05 pm

Hi Jefferson and Matt,

I didn't realize that the problem was the infamous 'Relationships may be needed' warning when I first answered your question Jefferson.

It is as Matt writes a false warning. (In this case at least). As Matt also writes you can't turn the warning off (no checkbox to do that) however you can remove it if it bothers you.

You can remove the warning by moving your measures to the bridge_table or Table B. You do that by right clicking the measure/edit formula and choose the bridge_table/table B instead of what you originally have chosen in the dialog. It may be necessary to click inside the pivot table to have Excel removing the warning after you have done this.
It is not neccesary to do so the measures will work anyway and IF you move them you might get problems with the drillthrough feature. (if you google drillthrough, Excel, DAX you can see what this is)

The explanation is the following.

In DAX the current filter context is always applied to a target table. In your case the target table is Table A (I assume the measure(s) counts, sums etc a column from Table A) and the filter context consists of a filter from table B. Filters from Table B can not directly filter Table A because filters automatically propagate from the one side to the many side and not vice versa. (Hence the need of the bridge table in your case so a table B filter filters the bridge table that then filters Table A through the expanded table mechanism that Matt explains).

Excel doesn't parse the measure to discover what the target table is nor does it take into account explicit filters like the bridge table argument in your CALCUALTE. Instead Excel uses a somewhat cruder method. It looks to see under which table the measure is saved then it looks at the row, column and slicer fields and from which tables those fields are. If just one field is from a table that doesn't filter the table under which the measure is saved the warning is shown. This is why the warning disappears when you save the measure under the bridge table or Table B. Table B filters can automatically filter the Bridge table/table B.

So in your case the warning is a false positive and you can ignore it. ( it does say 'may' the warning ).

jefferson airplane
Posts: 6
Joined: Wed Jul 25, 2018 7:05 pm

Re: Many to Many filter table problem

Postby jefferson airplane » Wed Aug 01, 2018 8:09 pm

Thank you both guys for your great support, it is clear now and I am ok with your solutions provided.

Do I need to edit the title with "Solved"?

Thanks


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 5 guests