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

The 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.

Reply
SadStatue
Helper II
Helper II

Visualising based on two level of calculation

Hi All,

 

I have fact table as below:

PersonLocationDate
Aa1/01/2020
Ab2/01/2020
Ac2/02/2020
Ad3/02/2020
Da1/01/2020
Ba2/01/2020
Bb2/02/2020
Cc3/02/2020
Cd4/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)

personnumber of locations
A4
B2
C2
D1

But what I want to visualize as the final step is the frequency of the number of locations as below:

 
 

Loc frequency.PNG

All in a dynamic manner so I can put a date Slicer

 

Thanks

 
2 ACCEPTED SOLUTIONS

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@SadStatue - What is the x-axis in your graph represent? Is that the location (a b c d)?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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