Filter Dataset Two Ways in Two Tables

[jeggen]

I am hoping for some help in figuring out how to accomplish the following.

I have the following two tables.

1. Partnerships - Lists all of our partnership with data in the following format. This is a list of unique values based on ID.
   ID | Name | Type
2. Meetings - Lists all meetings our staff has had with the following format. There is one line per meeting/particpant combination.
   Attendee ID | Date | Summary | Host ID | Participant ID

There are two ways a partner could show up in the meetings data. (1) If the meeting is with the partner the "Attendee ID" would be the partner ID. (2) If the meeting was with someone else where we represented the partner the partner ID would appear in the "Participant ID" field and the "Attendee ID would be the ID of an individual.

My goal is to create two tables in Power BI for these scenarios. One to list meetings with partners, and another to list meetings on behalf of the partners.

I thought about using relationships to create two single cardinality relationships between Partnerships -> ID and Meetings -> Attendee ID and a second between Partnerships -> ID and Meetings -> Participant ID. This doesn't work though, and I'm not sure it would actually produce the result I'm looking for.

I've also considered adding two measures in the Meetings table. One to determine if the meeting was with a partner, and then the second to select the appropriate ID based on whether the meeting was with a partner or on their behalf, looking up the existance of the Attendee ID in the Parter ID table. Then I could build a relationship to this column and filter each table on the meeting type. Problem is this will still be circular logic between these tables so I know power BI won't like this either!

Any suggestions on how to filter this data two different ways in two tables? I want to be sure the filter can be connected to a slicer so I can publish this for staff to produce their own reports for a partner by just using a slicer, so that makes it a bit more complciated too.

[mwegener]

Hi @jeggen ,

could you provide some sample data?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[jeggen]

See this power BI file:

https://www.dropbox.com/s/2lap5vpno4aqk1d/Example%20Data.pbix?dl=0

[mwegener]

Hi @jeggen ,

check this out.

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[jeggen]

@mwegener 

This seems to be what I am looking for, can you explain what you did to make this work?

[mwegener]

I have created an active and inactive relationship and controlled with the measures and the function userelationship, which should be used.

https://dax.guide/userelationship/

[jeggen]

I don't see any measures in the file you shared with me. Am I missing something?

Also in the real data it is not allowing me to do one:many relationships with the partnership id column, despite the fact that there are no duplictes. Will that cause any issues?

[mwegener]

Hi @jeggen ,

you don't see this?

[jeggen]

Sorry, I missed those somehow.

I now am able to get this to work in my real data but wondering if it's possible to hide some of these columns?

E.g. I would want to hide the participant and attendee columns since those would just add confusion to the end user. I also don't really need to show the partner name in either table, but am assuming this will not filter properly if those are gone.

[mwegener · Accepted solution]

Hi @jeggen ,

you could remove the partner name from the table, but the Measures participant and attendee are necessary.

But you can change the name an the value of this measures.

For example like this:

Attendee =
IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( Meetings ),
            USERELATIONSHIP ( Partnerships[Column1], Meetings[Attendee ID] )
        )
    ),
    BLANK (),
    " - "
)

 Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.