Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I'm having some issues with what I think should be a very easy task. I have a table as shown below, with the date a person went to work, the ID of said person and some characteristics of either them or the place, in this case let's say country.
What I need is to show in bar chart the amount of people that have gone to work a certain amount of days. something like the graph attached. This graph should be able to be filtered by date, only selecting a certain period, and by the other columns, for example if you only want to know this information in the country "A".
The issue I found is that I cannot create a measure to use in the x axis because PBI doesnt allow it, and I can´t use a regular column as my axis because then it wouldn´t be filtered by the date selected or another filter.
Graph:
Table:
Date| Person ID| Country
4/30/2023 | 3 | A |
4/30/2023 | 1 | A |
4/30/2023 | 1 | B |
4/28/2023 | 3 | B |
4/27/2023 | 5 | A |
4/26/2023 | 3 | A |
4/24/2023 | 3 | A |
4/23/2023 | 2 | A |
4/22/2023 | 2 | B |
4/21/2023 | 4 | B |
4/21/2023 | 2 | A |
4/20/2023 | 4 | A |
4/20/2023 | 2 | A |
4/19/2023 | 3 | A |
4/19/2023 | 1 | A |
4/18/2023 | 3 | A |
4/18/2023 | 3 | A |
4/16/2023 | 5 | A |
4/15/2023 | 3 | A |
4/15/2023 | 3 | B |
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your datamodel.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, what you wrote is pretty close to working thanks. The only issue is that COUNTROWS can give a higher number than expected because one person might have multiple rows for the same date, and i'd need to only count the date once (only if the connection was "In person" and On-Time ="Y"
In this next example it should return 1 for person 1 because they have at least one in person on-time connection that day. And 0 for person 2 because no row fulfills the requirements
Date Person ID Country Connection City On-Time
29/5/23 | 1 | A | Virtual | X | Y |
29/5/23 | 1 | A | In person | X | Y |
29/5/23 | 1 | B | Virtual | Y | N |
29/5/23 | 1 | A | In person | Z | Y |
29/5/23 | 2 | B | Virtual | Y | Y |
29/5/23 | 2 | A | In person | Z | N |
Thank you so much, I´ll work on this angle and see if I can get what they need.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |