Linking two dimension tables

[Dunner2020]

Hi there,

I got a dimension table of a personal record (let's called individual), and a qualification information table. I got two dimension tables: Authorisation and Individual qualification. The authorization table contains information about the license an individual holds, validation date of the license (i.e. start and expiry dates). The individual qualification contains information about the qualification the individual holds. An individual can have one or more licenses and qualifications. So, my data model looks like as follow:

I wanted to calculate the no of individuals (with valid licenses). My measure looks as follow:

Valid individual Record = CALCULATE(DISTINCTCOUNTNOBLANK(Authorisation[IndividualID]),FILTER(Authorisation,Authorisation[AuthorisationExpiryDate]>=NOW()))

Now I wanted to display the above measure w.r.t qualifications they have in the table visual. So I dragged the QualificationDescription from the individualqualification table and dragged it into table visual along with the above measure. The following picture show the result:

 As you can see it gives a total no of valid individuals for each type of qualification. It does not break down total individual w.r.t qualifications. I think the problem is that there is no link between two dimension tables. I am not sure how can I link two dimension tables together. Any suggestion would be really appreciated.

Sample file here

[v-jingzhang · Accepted solution]

Hi @Dunner2020 

Modify the cross-filter direction from Single to Both for the relationship between Individual table and IndividualQualification table. This will work. 

And in the table visual, drag the QualificationDescription column from Qualification table instead of individualqualification table, and use TODAY() to replace NOW() function in your measure. This will improve the speed for calculation. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Dunner2020 , I do not see the join between qualifications and Authorisation(Dimensions are fine. They do not join) , so that means cross join behavior.  You need have join of handle that in meausre