Relationship Filtering

[nchamilton2]

Good afternoon,
I have a Fact table with ProcedureID and ProjectID. I have a Project Table that connects to my fact table via ProjectId and is a one to many. My Procedure table connects to my Fact table via ProcedureId and is a one to many. I also have a NotesTable that has a ProcedureId. I can't connect my Fact table to my Notes table because it creates a many to many. I can connect my Notes table to the Procedure table which is connected to the Fact table and that connects fine. When i try to add Project, Procedure and a Note to my Table Visual I get an error that states "Can't determine relationships between the fields. I've verified my Primary Key's are all the same datatype. I've attempted to change the cross filter directions. Nothing is working.

This seems like a straightfoward model. Why is Power BI relationship filtering creating an issue? All of the other data anlaytics tools allow this same approach to work fine. The only other option I've found is stating to create a dax query that uses the TREATAS function. This seems like it should be easier than that. 

Can someone please help me understand why my approach won't work. It's a simple 4 table model.

[speedramps]

I want to help you but your description and limitted example daya is too vague. 

Please ask you manager, a colleague or your teacher (if you are a student) to help youiwrite the question more clearly with some contect.

Also look at the exmaples that I and other helpers have alreday provided

The sample data you have provdied is pretty wowful. They is no point having dimensions for 1:1 reltationship if there is no lookup data. 😀😀😀

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[ajaybabuinturi]

Hi @nchamilton2,

I hope you have data model below. There is no filteration from Fact to Procedure to Notes. All these are single direction filter. You need to use/try Bidirectional filter to filter the data based Project. Please let me know if you have any questions.

Modified Model

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[nchamilton2]

Thanks for the reply. Yes I tried that solution.

[ajaybabuinturi]

@nchamilton2 Is that helps you?

[nchamilton2]

No it didn't work, but  thank you.

[v-aatheeque]

Hi @nchamilton2 ,

Thank you for bringing this up, and apologies for the inconvenience you're facing.

As some of the community members have already shared helpful suggestions, we’d like to assist you further  but to do so effectively, we kindly request that you provide sample data that illustrates the issue clearly and completely.

Make sure not to include sensitive or unrelated data.This will help us reproduce your scenario and provide an accurate solution tailored to your issue.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Looking forward to your response!

[v-aatheeque]

Hi @nchamilton2 ,

To provide you with the most effective solution, I had earlier requested a sample of your data. Even a simplified version would be helpful in understanding the issue better and tailoring a workaround that fits your scenario.

If the issue still persists, please share a small example so we can assist you further.

If you need further clarification or continue to face issues, feel free to reply  we're here to help!

Thank you for being an active member of the Microsoft Fabric Community!

[speedramps]

I wnat to help you but your question is vague and with no exmple data.

I have therefore created a mock example where each procedure can have multipe note recods explaining which team members are required for each  procedure .

As you can plainly see, there is no logical ink between Notes and Facts, which is why you get the error.

How you can have many to may like this

I want to help you more but your description is too vague. Please write it again.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[nchamilton2]

Thanks for the reply. Power BI is the only tool I've ever used where you have to have a direct relationship to the fact table. Generally in other tools, as long as the tables are related in some way (like I've described) the relationship flows through. Thanks for replying back I appreciate it. 

So, would you mind offering a solutions based on the following data?

Fact:
ProcedureID    ProjectID
1              100
2              100
3 200

Project
ProjectID
100
200

Procedure
ProcedureId
1
2

Notes
NoteID   ProcedureId
1             1
2             1
3             2