I am hoping for some help in figuring out how to accomplish the following. I have the following two tables.Partnerships - Lists all of our partnership with data in the following format. This is a list of unique values based on ID.ID | Name | TypeMeetings - 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 IDThere 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.

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,MarcusDortmund - GermanyIf I answered your question, please mark my post as solution, this will also help others.Please give Kudos for support.

jeggen

Helper II

6 years ago

Solved

Filter Dataset Two Ways in Two Tables

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

I have the following two tables.

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
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.

Need Help

mwegener
6 years ago
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.

Forum Discussion

Filter Dataset Two Ways in Two Tables

9 Replies

Related content

Filtered table

Can I filter the 1st fact table which then filters a 2nd fact table?

Filtering Table report

How to search specific word in table filter Calculations

Refresh dataset api