Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SuchCT
Helper II
Helper II

show sum in a bar chart based on date and table filters

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:

SuchCT_0-1684811252202.png

 


Table:
Date|                    Person ID|         Country

4/30/2023          3                    A
4/30/20231A
4/30/20231B
4/28/20233B
4/27/20235A
4/26/20233A
4/24/20233A
4/23/20232A
4/22/20232B
4/21/20234B
4/21/20232A
4/20/20234A
4/20/20232A
4/19/20233A
4/19/20231A
4/18/20233A
4/18/20233A
4/16/20235A
4/15/20233A
4/15/20233B

 

3 REPLIES 3
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1684819813545.png

 

Jihwan_Kim_1-1684820124952.png

 

 

 

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.


Go to My LinkedIn Page


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/231AIn personXY
29/5/231BVirtualYN
29/5/231AIn personZY
29/5/232BVirtualYY
29/5/232AIn personZN

Thank you so much, I´ll work on this angle and see if I can get what they need.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.