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.
Hi All,
Hope you are all well, My Friday brain has kicked in and cannot figure this one out. So i am building a office occupancy report and was asked if i could add a staff frequency visual. i have the below which when i use dept filters and weekly filters will show how many times that person was in this week. I have a data set where i count if a user had a flag activiated on a keypass panel for exit of said building, if so then they were in that day. I am using distinctcount to show 1 entry per day recorded per person as one person could exit 27 times but i just need one instance. I can get a total count (how many per day) as per below but i want a count per column on the right and if the count is 0 or 1 in any given week i can highlight and ask the "why" . i want the count to reflect for example from below
Row 1 = 2
Row 2 = 4
Row 3 = 1
When i turn on column subtotals and row subtotals I get a value of 1 on everything so i used the below dax but this gave me the total for rows not columns
I have a table called "Attendance MD"
fields i am using are:- Full name, Event (distinct Count)
Thanks in advance and Happy new year
Solved! Go to Solution.
Hi @Brookied1974 ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _table1=
SUMMARIZE('Attendance MD','Attendance MD'[Event],"Value",[Event Count])
var _if=
IF(
ISINSCOPE('Attendance MD'[Event]),[Event Count],SUMX(_table1,[Value]))
var _table2=
SUMMARIZE('Attendance MD','Attendance MD'[Full name],"Value",[Event Count])
return
IF(
ISINSCOPE('Attendance MD'[Full name]),_if,SUMX(_table2,[Value]))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
HI, thanks a million, that worked although i did make a slight change to my dataset as i had multiple events on the same day so i did some clean up to help the measure. Thanks though.
Hi @Brookied1974 ,
Here are the steps you can follow:
1. Create measure.
Measure =
var _table1=
SUMMARIZE('Attendance MD','Attendance MD'[Event],"Value",[Event Count])
var _if=
IF(
ISINSCOPE('Attendance MD'[Event]),[Event Count],SUMX(_table1,[Value]))
var _table2=
SUMMARIZE('Attendance MD','Attendance MD'[Full name],"Value",[Event Count])
return
IF(
ISINSCOPE('Attendance MD'[Full name]),_if,SUMX(_table2,[Value]))
2. Result:
Best Regards,
Liu Yang
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 |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |