Date Filters From Multiple Tables

[bpriebe]

Hello everyone,

I have two fact tables connected by User ID and would like to display data that respects a date filter from each table.

Courses:

UserID

CourseName

CompletionDate

Claims:

UserID

ClaimDate

CompanyName

CompanyState

Amount

I would like to display summed data that respects a date slicer from each table while also splitting the users into groups based on whether they had courses and/or claims during the selected date ranges.

For example, if the courses range is set to 1/1/2021 to 1/31/2020 and the claims range is set to 12/1/2020 to 2/28/2021, I need to show the following:

Group 1 - courses and claims during the ranges

Number of users, number of courses, number of claims, total amount

Group 2 - claims but no coures

Number of uses, number of claims, total amount

Group 3 - courses but no claims

Number of users, number of courses

I have tried various options but have had no luck. Any help is greatly appreciated.

Thank you,

Bristol

[amitchandak · Accepted solution]

@bpriebe , Create a common user dimension https://www.youtube.com/watch?v=Bkf35Roman8

and try measure like

Group 1

Courses users = count(Courses[UserID])

Courses =count(Courses[CourseName])

number of claims= count(Claims[Amount])

total amount= sum(Claims[Amount])

Group 2
Group 2 Number of uses= if(isblank([Courses users]) ,count(Claims[UserID]), blank())

Group 2 Number of claims= if(isblank([Courses users]) ,[number of claims], blank())

Group 2 total amount = if(isblank([Courses users]) ,[total amount], blank())

Group 3
Group 3 Number of uses= if(isblank([number of claims]) ,count(Claims[UserID]), blank())

Group 3 Number of claims= if(isblank([number of claims]) ,[number of claims], blank())

Group 3 total amount= if(isblank([number of claims]) ,[total amount], blank())

Group 2 and 3 measure can be enclosed in sumx

example 

sumx(User[UserID] , if(isblank([number of claims]) ,[total amount], blank()))

[bpriebe]

@amitchandak 

Thank you for the quick response! I have the bridge table completed as shown in the linked video. The measures for claim count, course count, and total amount are working perfectly but the user count measures are displaying figures that are too large. Any thoughts on what may fix those measures?

[bpriebe]

I have determined the issue with the user count measures and all appears to be working as hoped. Thank you very much!