Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I have fact table as below:
Person | Location | Date |
A | a | 1/01/2020 |
A | b | 2/01/2020 |
A | c | 2/02/2020 |
A | d | 3/02/2020 |
D | a | 1/01/2020 |
B | a | 2/01/2020 |
B | b | 2/02/2020 |
C | c | 3/02/2020 |
C | d | 4/02/2020 |
We have 4 person who works in different locations I need to calculate the number of locations they are working in dynamicaly (as I need to put a date slicer)
person | number of locations |
A | 4 |
B | 2 |
C | 2 |
D | 1 |
But what I want to visualize as the final step is the frequency of the number of locations as below:
All in a dynamic manner so I can put a date Slicer
Thanks
Solved! Go to Solution.
@SadStatue , Not very clear. But see if binning and segmentation can help
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...
@SadStatue - OK, starting to get to the heart of this issue now. You, my friend, will need a disconnected table. It's the only way that I know of to do what you want to do. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
Basically, you need a disconnected table to be your axis and then your measure forms the relationship between the disconnected tables. It's a bit of a mind bender to wrap your head around the first time but once you get it, it will be second nature...famous last words.
@SadStatue , Not very clear. But see if binning and segmentation can help
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...
@SadStatue - What is the x-axis in your graph represent? Is that the location (a b c d)?
It is the second column of the second table "Number of location". it shows that a person is took part in how many location.
I hope I could describe it!
@SadStatue - OK, I have mocked this up, seems to work. Requires a bi-directional relationship. Still not sure if it is what you are looking for. See attached PBIX file, you want Page 38, Table (38) and Table (38a).
@ me in your response.
@Greg_Deckler- Thanks for your work on this issue.
The second table (38a) is not a static table it is calculated from the fact table. so when I choose 1/1/2020 on the slicer there should be 2 person in 1 (in X axis) not 1 person in 1, and 1 in 4.
what I want is to calculate 38a[number of locations] based on the date range is selected in slicer. and visualize it in such a graph.
Cheers
@SadStatue - OK, starting to get to the heart of this issue now. You, my friend, will need a disconnected table. It's the only way that I know of to do what you want to do. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
Basically, you need a disconnected table to be your axis and then your measure forms the relationship between the disconnected tables. It's a bit of a mind bender to wrap your head around the first time but once you get it, it will be second nature...famous last words.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |