Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have 3 Tables. Violations, Logs, and Company
Tables look like this
Violations:
Unique ID Company ID Employee ID Violation ID Date
987 2 AAA 51 2/8/2019
988 2 BBB 51 2/11/2019
989 3 CCC 14 3/26/2019
Logs: (There should basically be 1 log for each employee of each company for each day. Below is just an example)
Company ID Employee ID Log Date Hours Worked
2 AAA 2/8/2019 15
2 AAA 2/9/2019 8
2 AAA 2/10/2019 12
2 BBB 2/1/2019 9
3 CCC 2/2/2019 10
Copmany:
Company ID Company Name
2 Red Company
3 Blue Company
4 Green Company
So basically I have a table with hours worked for each company, employee, and day; and I have a table of violations that stems from those logs. Not every log necesarily has a violation, but every violation should be tied to a specific log if that makes sense.
What I am trying to figure out is how to take a percentage of logs that contain each specfic type of violation for each company. So for example, If Red company has 8 violations of Violation ID 51 on a given day, and 100 total logs for that day, I would get 8%
I know how to calculate that part, but I want to be able to adjust the date range and have both tables filter accordingly. So If I want to look at Violation ID 51 for Red Company for the entire month, the measure would now calculate (for example) 250 violations and 2500 logs...10%.
When I've added a date slicer to do this, it seems to only filter the violation table for the specified dates, not the Logs table too. If I create a separate date table and build relationships to that, both tables will not filter with a company slicer.
Any ideas?
Solved! Go to Solution.
I understand you correctly, please refer to the following steps:
1. create three tables, leave these tables no relationship with any other table
A-calendar = CALENDARAUTO()
B-company filter table = VALUES(Copmany)
C-violation filter table = VALUES(Violations[Violation ID ])
2, Create measures in 'Violations" table
min date = MIN('A-calendar'[Date]) max date = MAX('A-calendar'[Date])
selected company = SELECTEDVALUE('B-company filter table'[Company ID]) selected violation = SELECTEDVALUE('C-violation filter table'[Violation ID ])
count selected = CALCULATE ( COUNT ( Violations[Unique ID] ), FILTER ( ALLSELECTED ( Violations ), Violations[Date] >= [min date] && Violations[Date] <= [max date] && Violations[Company ID] IN { [selected company] } && Violations[Violation ID ] IN { [selected violation] } ) ) count all =
CALCULATE (
COUNT ( Logs[Log Date] ),
FILTER (
ALL ( Logs ),
Logs[Company ID] = [selected company]
&& Logs[Log Date] >= [min date]
&& Logs[Log Date] <= [max date]
)
)
% = [count selected]/[count all]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
I understand you correctly, please refer to the following steps:
1. create three tables, leave these tables no relationship with any other table
A-calendar = CALENDARAUTO()
B-company filter table = VALUES(Copmany)
C-violation filter table = VALUES(Violations[Violation ID ])
2, Create measures in 'Violations" table
min date = MIN('A-calendar'[Date]) max date = MAX('A-calendar'[Date])
selected company = SELECTEDVALUE('B-company filter table'[Company ID]) selected violation = SELECTEDVALUE('C-violation filter table'[Violation ID ])
count selected = CALCULATE ( COUNT ( Violations[Unique ID] ), FILTER ( ALLSELECTED ( Violations ), Violations[Date] >= [min date] && Violations[Date] <= [max date] && Violations[Company ID] IN { [selected company] } && Violations[Violation ID ] IN { [selected violation] } ) ) count all =
CALCULATE (
COUNT ( Logs[Log Date] ),
FILTER (
ALL ( Logs ),
Logs[Company ID] = [selected company]
&& Logs[Log Date] >= [min date]
&& Logs[Log Date] <= [max date]
)
)
% = [count selected]/[count all]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |