Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
4 years ago
Solved

Linking two dimension tables

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

 

Need Help
Show and Tell
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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's avatar
    amitchandak
    Super User
    4 years ago

    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