Unable to add(sum) the flags set to 1 in PowerBI Matrix visualzation

[deb_power123]

Hi All,

I am facing an issue in PowerBI matrix visualization.I have a school table with column values Student_ID,Location and AttendanceDate.

I need to find the sum of the number of times each student who attended classes >=1 days per location per month.

I have created a custom measure named Attendance as stated below to calculate students who the attended classes >=1

Attendance = IF(DISTINCTCOUNT(school[Attendance_Date])>=1,1,0)

In my visualization, I am able to get all the flags which is set to '1' for all the students who meet the condtion of attending classes>=1.But as per my requirement  I want to get the sum of these 1 flags to get the number of times all students attended classes >=1 per location per month.My final visulization should not contain the student ID, it should only have the location and months and the sum of the flags set to 1 indicating the number of times students attended the classes >=1 .

Expected Output:-

Location    January   February March 

Chennai       1             1             1

Delhi            2              2             2

Goa              0              2             0

I tried to implement the fixed LOD concept as we do in tableau to handle this scenario in PowerBI but no luck.

I created a  calculated measure 'CalculateAttendance as below but it is not working :-

Calc ulateAttendance = CALCULATE((school[Attendance]),ALLEXCEPT(school[Student_ID],school[Location],school[Attendance]))

Could you please provide any changes to my above calculations to resolve this issue.Please help. I wanted to add .pbix file too but this post doesnt have an insert file option.I have added the data source excel and the present visualization screenshot as below.

Regards

Sameer

Current  Matrix Visualization screenshot:-

Input data source excel:

Attendance	StudentID	Location
01.01.2017	100	Delhi
02.01.2017	100	Delhi
03.01.2017	100	Delhi
04.01.2017	100	Delhi
05.01.2017	100	Delhi
06.01.2017	100	Delhi
01.01.2017	101	Delhi
02.01.2017	101	Delhi
03.01.2017	101	Delhi
04.01.2017	101	Delhi
05.01.2017	101	Delhi
06.01.2017	101	Delhi
08.01.2017	101	Delhi
09.01.2017	102	Chennai
01.01.2017	102	Chennai
02.01.2017	102	Chennai
03.01.2017	102	Chennai
04.01.2017	102	Chennai
05.01.2017	102	Chennai
06.01.2017	102	Chennai
08.01.2017	102	Chennai
11.01.2017	102	Chennai
01.02.2017	101	Delhi
02.02.2017	101	Delhi
03.02.2017	101	Delhi
04.02.2017	101	Delhi
05.02.2017	101	Delhi
06.02.2017	101	Delhi
01.02.2017	100	Delhi
02.02.2017	100	Delhi
03.02.2017	100	Delhi
04.02.2017	100	Delhi
05.02.2017	100	Delhi
06.02.2017	100	Delhi
01.02.2017	102	Chennai
02.02.2017	102	Chennai
03.02.2017	102	Chennai
04.02.2017	102	Chennai
05.02.2017	102	Chennai
06.02.2017	102	Chennai
01.02.2017	103	Goa
02.02.2017	103	Goa
03.02.2017	103	Goa
04.02.2017	103	Goa
05.02.2017	103	Goa
06.02.2017	103	Goa
01.02.2017	104	Goa
02.02.2017	104	Goa
03.02.2017	104	Goa
04.02.2017	104	Goa
01.03.2017	100	Delhi
02.03.2017	100	Delhi
03.03.2017	100	Delhi
04.03.2017	100	Delhi
05.03.2017	100	Delhi
06.03.2017	100	Delhi
01.03.2017	101	Delhi
02.03.2017	101	Delhi
03.03.2017	101	Delhi
04.03.2017	101	Delhi
05.03.2017	101	Delhi
06.03.2017	101	Delhi
08.03.2017	101	Delhi
09.03.2017	102	Chennai
01.03.2017	102	Chennai
02.03.2017	102	Chennai
03.03.2017	102	Chennai
04.03.2017	102	Chennai
05.03.2017	102	Chennai

[v-janeyg-msft · Accepted solution]

Hi, @deb_power123 

According to your description, It's not hard to do. You can use 'DISTINCTCOUNT' in ID column instead of date column.

Like this:

Measure 2 = DISTINCTCOUNT(TableA[StudentID])

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@deb_power123 , Try one of the measure

countx(values(Table[Location]), calculate(distinctCOUNT(Table[StudentID])))

or

countx(summarize(Table, Table[Location], Table[Date].Month, "_1",calculate(distinctCOUNT(Table[StudentID]))),[_1])