Forum Discussion
Date Filters From Multiple Tables
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
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()))
3 Replies
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()))
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?
I have determined the issue with the user count measures and all appears to be working as hoped. Thank you very much!
