Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Probably a really simple one but my brain is a little fried today.
I am trying to count the number centres in my dimension table however want to account for closures (Divestment Date) so for example a centre closes in 2020 so it would be present in 2019 and 2020 but not present in 2021 and 2022.
Trying to get the below table to reflect the closed centres, calendar year is taken from the Calendar table above.
Solved! Go to Solution.
Hi @JP8991 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a measure.
Centres =
var _a=
CALCULATE (
COUNTROWS ( Centres ),
FILTER (
ALL ( Centres[Divestment Date] ),
Centres[Divestment Date] <= MAX ( Centres[Divestment Date] )
)
)
var _b=COUNTROWS(FILTER(ALL('Centres'),'Centres'[Divestment Date]))
return
_a-_b
(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @JP8991 ,
Your screenshot doesn't show the relationship between Centres and Calendar but if you're getting the same figure for all calendar years then mostly likely that there isn't. You can try this as a measure but still won't show the correct number if you use calendar years and there isn't a relationship between the two tables
=
CALCULATE (
COUNTROWS ( Centres ),
FILTER (
ALL ( Centers[Divestment Date] ),
Centers[Divestment Date] <= MAX ( Centers[Divestment Date] )
)
)
Proud to be a Super User!
Thanks, really appreciate it, however it isn't quite working.
Centres =
CALCULATE (
COUNTROWS ( Centres ),
FILTER (
ALL ( Centres[Divestment Date] ),
Centres[Divestment Date] <= MAX ( Centres[Divestment Date] )
),
USERELATIONSHIP ( 'Calendar'[Date], Centres[Divestment Date] )
)
Using the above code I am getting the table below which is counting the number of closed Centres, what I am after is the number opposite to that, I am after the amount that were open.
So in 2019 it would be 45 minus total, in 2020 it would be 45 + 7 minus total etc etc.
Hi @JP8991 ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a measure.
Centres =
var _a=
CALCULATE (
COUNTROWS ( Centres ),
FILTER (
ALL ( Centres[Divestment Date] ),
Centres[Divestment Date] <= MAX ( Centres[Divestment Date] )
)
)
var _b=COUNTROWS(FILTER(ALL('Centres'),'Centres'[Divestment Date]))
return
_a-_b
(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@JP8991 , Seem like join is missing between Divestment Date, or create an inactive join and userelationship
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |