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
In the example below, I'm trying to create a measure to count the number of SiteRefs where the cumulative value of size is greater than 0:
So the correct answer would be 4 for January, 3 for February, 2 for March and so on. I have a date table and have created measures for site size and cumulative site size (below), and am trying to create a third calculated count measure using cumulative site size > 0 as a filter, but keep getting an error (cannot use values of tru/false). Can anyone help?
Data:
SiteRefEventDateSize
A | 01 January 2020 | 10000 |
C | 01 January 2020 | 4500 |
A | 15 January 2020 | -5000 |
C | 15 January 2020 | 500 |
D | 15 January 2020 | 500 |
E | 15 January 2020 | 1000 |
A | 15 February 2020 | 2000 |
C | 15 February 2020 | 500 |
D | 15 February 2020 | -500 |
E | 15 February 2020 | -1000 |
F | 15 February 2020 | 200 |
A | 15 March 2020 | -7000 |
C | 15 March 2020 | 600 |
E | 15 April 2020 | 500 |
F | 15 May 2020 | 100 |
Solved! Go to Solution.
You should never attempt such a problem without a dimension table on the site.
Try this
@MattAllingtonany ideas? I thought this would be simple to solve although its proving much more difficult than I thought
You should never attempt such a problem without a dimension table on the site.
Try this
Fantastic @MattAllington this is perfect, exactly what I needed! Thank you so much.
Thanks Greg. Sorry probably wasn't 100% clear. The matrix in the original post shows the cumulative values by month for each SiteRef. What I am trying to do is come up with a way to count the number of SiteRefs in each month that have a cumulative size greater than zero.
Not sure what you are referring to by [column] in your reply?
@Greg_Deckler following your advice I've got part of the way there! I've created a measure which works without context....what I cannot work out is why an error is produced when context is added
Regards
Andy
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |