Count records using filter related tables

[SoufTC]

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,

[jdbuchanan71]

@SoufTC 

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.

[SoufTC]

Hi @jdbuchanan71 thank you for your answer i tried it but it still doesn't work !

[DataZoe]

Hi @SoufTC 

It looks like there is not a filter path from Calendar which is why the measure is not showing, but you can correct it with this measure:

number of report by user =
CALCULATE (
COUNT ( REPORT_USER[ID_CREA] ),
CROSSFILTER ( USER[ID_USER], AGENDA_USER[ID_USER], BOTH )
)

[SoufTC]

Hi @DataZoe Still not working Thank's

Bof,

[DataZoe]

@SoufTC Can you provide more details? From what you have shown I am not sure what else to suggest. 

[SoufTC]

@DataZoe  I tried to modify the question thank's

Bof,

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  In that file, show the problem and also the expected result.

[SoufTC]

firstly I would like to know if the measurements were well created  : the number of reports per user and the number of reports per day worked then to know why the filter date does not work on the measurement report per day worked

https://drive.google.com/drive/folders/17DEqKseEVGO9Q62L5Titj4WDUgD5bCli?usp=sharing

[Ashish_Mathur]

Hi,

There should be a relationship from one of the Date columns of the Report_user table to the Date table.  Before, that you will have to remove the time stamp from the Date columns of the Report_user table.  Also, all relationships should have a Cardinality of Many to One and Cross filter direction of Single.  Lastly, the measure should be as simple as

NUMBER OF REPORT = COUNT (REPORT_USER[ID_USER] )