Indirectly filter a table

[BenSettle]

=[ EDIT ]=

Thanks for your replies, those saying it all worked as shown are correct. Unfortunately I had failed to replicate my real world example sufficiently.

I don't actually have a "Training_plan" column in my courses table, so there is no link. Instead, there is a bridge table that expands a sub-table from training plans, where each plan contains an array of courses within it. This bridge table is somehow broken, causing the issue I'm experiencing but not in this example. I may explain further another time. 

-=====

Hi, 

I have three tables regarding users training plans, courses, and enrollments. Each training plan contains multiple courses, and each course has multiple enrolments. The enrolments only reference the course, and not the training plan.

Tables:

Model:

I'm trying create a table of enrolments filtered training plan,  but when I add the filter the data doesn't change.

I understand that this is because there is no direct link between Table A and Table C.

Is there an easy way to archieve this indirect filter? 

Apologies if my termononolgy is all wrong, I'm new around here! 

Thanks in advance,

Ben 

[_AAndrade · Accepted solution]

Hi @BenSettle,

I replicated your data set and it's working fine. Take a look.
According of your example the Plan 1 have Course 1 and 2 and the corresponding enrolements are 211,212,213,214,215 and 216 like I have in this picture:

If I choose Plan2 the output must be blank because in our example we don't have any enrolement and as you can see in the picture below is working:

I also attaching my pbix file so you can take a look. If you need any help please let me know.

[v-xianjtan-msft · Accepted solution]

Hi @BenSettle 

Agree with _AAndrade. Your model seems to be fine.

Regarding your concern about filter, I think it's because you don't have enough data in your Table C - enrolments table sample, your table currently only contains course information for Plan 1, so when you select Plan 1 in the slicer, nothing happens to the data in your table visual, and when you select Plan 2 or Plan 3, the table visual returns blank, as the _AAndrade reply to the screenshot shows.

I'm trying create a table of enrolments filtered training plan,  but when I add the filter the data doesn't change.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bibiano_Geraldo]

Hi, in your enrolments Table, Lookup the training plan into your course Table, based on idCourse, and then add a relationship between your training plan and enrolments Table.

Something like this:
In your errolments table, add calculated column and pase the following DAX:

Training_Plan =
VAR CourseToFind = enrolments[Course]
RETURN
    LOOKUPVALUE(
        Course[Training_Plan], 
        Course[Course], 
        CourseToFind
    )

Dont forget to replace the columns and table name with your owns.

if this help you, please mark this answer as solution.

Thank you

[_AAndrade · Accepted solution]

Hi @BenSettle,

I replicated your data set and it's working fine. Take a look.
According of your example the Plan 1 have Course 1 and 2 and the corresponding enrolements are 211,212,213,214,215 and 216 like I have in this picture:

If I choose Plan2 the output must be blank because in our example we don't have any enrolement and as you can see in the picture below is working:

I also attaching my pbix file so you can take a look. If you need any help please let me know.

[BenSettle]

Thanks for your reply. Unfortunately I had failed to replicate my real world example sufficiently.

I don't actually have a "Training_plan" column in my courses table, so there is no link. Instead, there is a bridge table that expands a sub-table from training plans, where each plan contains an array of courses within it. This bridge table is somehow broken, causing the issue I'm experiencing but not in this example. I may explain further another time. 

[_AAndrade]

Hi @BenSettle,

My post help you to solve your problem? If so, please mark my post as solution for your question.
Thank you