Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
12 | |
9 | |
9 | |
9 |
User | Count |
---|---|
21 | |
14 | |
14 | |
13 | |
13 |