Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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,
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 !
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 )
)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@SoufTC Can you provide more details? From what you have shown I am not sure what else to suggest.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hi,
Share the link from where i can download your PBI file. In that file, show the problem and also the expected result.
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
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] )
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
78 | |
35 | |
35 |
User | Count |
---|---|
156 | |
103 | |
71 | |
66 | |
53 |