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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yanivcogan
Frequent Visitor

Relationship Structure for Both Non-Hierarchical Tags and Hierarchical Categories

Hello,

I'm working on a data model that contains data about courses that my organization uses. Each course is assigned to a single "project" (a project can contain multiple courses). Each project has zero or more available "tags" associated with it (for example, a project can be associated multiple possible tags for target age groups, and with multiple tags for geographical location - tags have no hierarchical relationship to each other).

Every course, in addition to being assigned a project, can be associated with any number of tags from the available selection of tags for the project (i.e. the tags associated with that project).

 

For example, we can have some project "project 1", which contains two courses, "course 1" and "course 2"; "project 1" is associated with three tags - "tag 1", "tag 2", "tag 3". Each of the courses inside "project 1" can then be assigned a subset of "tag 1", "tag 2", "tag 3". If we had another project, "project 2", courses within "project 2" wouldn't have access to tags 1 through 3.

 

So, when it comes to cross visualization filtering, or for slicer behavior, the effect I would like to achieve is having one slicer on the project level, and one slicer for the tag level - when selecting a project in the project slicer I'd like to filter out all courses that aren't assigned to this project, and all project tags that aren't assigned to the project (from the tag level slicer).

When selecting a tag, or a number of tags, I would like to further narrow down the filtering criteria to keep only courses with one of the selected tags.

 

When attempting to define this behavior in the relationship editor, I got stuck:

 tag system relationships structure.jpg

The editor doesn't allow me to connect courses both directly to a project and to project tag, since project tags are themselves directly connected to projects (and that would create an infinite loop when it comes to filtering down data).

 

If I allow the course-project connection to remain inactive the problem becomes that for courses without any tag, the course-project connection doesn't register.

 

I would love to hear ideas on how to achieve the appropriate behavior.

Thanks,

   - Yaniv

4 REPLIES 4
v-yuezhe-msft
Employee
Employee

@yanivcogan,

In your scenario, you are only able to create inactive relationship between project table and course table. Assume that you are allowed to make this relationship active, in this case, you create an ambiguous path from project table to course-tags table, should project traverses tags table or course table?

When you have inactive relationship between tables, you can create measures using USERELATIONSHIP function to get desired result.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for responding, that certainly looks like the right direction to follow. So far, I've only managed to user USERRELATIONSHIP to calculate aggregations of values based on an inactive relationship. I can't seem to figure out how to just list the relevent data, without summeraizing it in any way. I tried using the VALUES function, as suggested here, but got an error message () when trying to use the measure with any visual.

I tried using LOOKUPVALUE as suggested in some places, but it looks like that is currently only posibble if there is an active relationship between the tables involved.

 

Any suggestions on how to proceed from here would be very much appreciated. Again, thanks @v-yuezhe-msft for taking the time to respond.

@yanivcogan,

Please help to share sample data of your tables and post expected result here.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

Hello again, really sorry about the ridicoulus delay in getting back to you.

Here is a PBI file with example data in the same structure: Link

 

I can't figure out how to get either LOOKUPVALUE or USERRELATIONSHIP to return an actual list of courses, which I can display in a table - only an aggregation for numerical fields.

 

Ideally, I would like to have the right-most slicer (the project slicer) filter both the other slicer (the tags slicer) and the courses table, and have the tags slicer filter only the courses table.

 

If that is impossible, and the problem is that this data stracture is not normalized, how would I go about fixing that, to make it more PBI compatible?

 

Thanks,

     Yaniv

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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