The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
My PBI is linked to a Sharepoint which is populated by a Power Apps.
The PowerApp is updated once a day, and is meant for Tier reporting. During the submission of the PowerApp, the user selects a field with multiple checkbox to select which departments/functions are present for the meeting that day. This then translates to a Option column in Sharepoint with "A,B,C,D,E".
I converted this in PBI to a Matrix, and there is a separate query with 2 columns. 1 with the unique ID that corresponds to each meeting, and the other reflects the departments present for that meeting.
Eg. (ABC present for meeting 1, AB present for meeting 2)
ID Department
1 A
1 B
1 C
2 A
2 B
I want to create a query which trends the attendance of each department. I managed to come up with a measure that counts the number of meetings attended per function:
Solved! Go to Solution.
I agree with what the video told you. If I understand then, your attendence table looks like this
If that is the case then you can take the distinct count of the meeting id to get the number of meetings. Which is what this formula does: calculate(DISTINCTCOUNT(attendanceTable[ID]),All(attendanceTable))
I am thinking the error is occuring when you try to do a distinct count from the other table.
Proud to be a Super User! | |
Do you have an attendance table and a Tier 2Data table or is it just one table you are working with?
Proud to be a Super User! | |
Hi,
To clarify, I have 2 tables.
So the original field I get from PowerApps concantenates all the different departments together. I watched a video and was told that it would be best to split out that column and create a new table which is illustrated in the images in the original post.
I agree with what the video told you. If I understand then, your attendence table looks like this
If that is the case then you can take the distinct count of the meeting id to get the number of meetings. Which is what this formula does: calculate(DISTINCTCOUNT(attendanceTable[ID]),All(attendanceTable))
I am thinking the error is occuring when you try to do a distinct count from the other table.
Proud to be a Super User! | |
That worked! Thank you!
If I wanted to then create a filter to drill down the percentage such that it will change according to the month, may I know how I can do that?
Happy it worked for you.
If you want to filter by date you will need to have whichever table contains both meeting id and the date the meeting was held related to the attendence table by meeting id.
You would then be able to use the meeting dates to filter the attendence results.
Proud to be a Super User! | |
Another quick question,
As I am pulling out the concantenated values and pulling it into the "Attendance" table, only the departments that are selected in the PowerApp (present departments) are reflected in the table.
Is there a way where I can pull ALL departments into the table, but reflect "present" if they are selected, and "absent" if they are not? I think this would make it easier to get the drilldown results that would fit my usecase.
If there is a relationship between the master department list and the attendence table you can put the master department list column in your visual that has the attendence measure and select Show items with no data
you should end up with something like
Proud to be a Super User! | |
You can try;
Proud to be a Super User! | |
Hi,
Unfortunately I get the same result as follows:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |