Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jeggen
Helper II
Helper II

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.

  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.

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


View solution in original post

9 REPLIES 9

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


@mwegener 

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

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/

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


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?

Hi @jeggen ,

 

you don't see this?

ParticipantAttendee.png

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


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.

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.