Forum Discussion
Count records using filter related tables
Hi everyone,
I'm trying to build a dashboard that contains a date page filter and that calculates the number of reports per user and also per day worked.
Here is the schema of my tables:
Calendar table :
| Date | Year | Month |
| 2020-01-01 | 2020 | 2 |
Agenda table :
| Date | ID_USER | Value |
| 2020-01-01 | 1 | 1 |
Value : 0 = user dont work ; 1=user work
User table :
| ID_USER | NAME |
| 1 | Jack |
Report table :
| ID_USER | DATE | RESULT |
| 1 | 2020-01-01 | good |
Result I try to find :
| USER | DATE | REPORT | WORK | REPPORT/WORK |
| 1 | 2020-01-01 | 1 | 1 | 1/1 = 1 |
To calculate the number of reports per user (report_user table) I try this :
number of report by user = COUNT(REPORT_USER[ID_CREA])
To calculate the number of reports (report_user table) per day worked(agenda_user table) I try this :
number of report by user = number of report by user /SUM(AGENDA_USER[Value])
When I use the date filter(Calendar table) it doesn't work on number of report by user
Could anyone highlight what I am missing to make the append statement work?
Thank's in advance,
bof,
9 Replies
Hi,
Share the link from where i can download your PBI file. In that file, show the problem and also the expected result.
The problem is filters on the date table flow down to the AGENDA_USER table but do not flow up to the USER table. If you set the highlighted relationship to be bi-directional (filter in both directions) it should work.
Hi jdbuchanan71 thank you for your answer i tried it but it still doesn't work !
