Forum Discussion
Using a measure as a filter for another measure
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 |
You should never attempt such a problem without a dimension table on the site.
Try this
Measure = SUMX(VALUES(UniqueSites[Site]),if([Site Size Cumulative] >0,1,0))Note the new dimenstion table called UniqueSites
6 Replies
- Greg_Deckler
Community Champion
Not sure I am 100% understanding but typically you would do something along the lines of:
VAR __Table = 'Table' //current table rows in context
VAR __Table1 = ADDCOLUMNS(__Table,"Measure",[Some measure])
RETURN
COUNTX(FILTER(__Table1,[Measure] > 0),[Column])
Something along those lines, sometimes you need to do a SUMMARIZE for your initial table.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
MattAllingtonany ideas? I thought this would be simple to solve although its proving much more difficult than I thought
- MattAllington
You should never attempt such a problem without a dimension table on the site.
Try this
Measure = SUMX(VALUES(UniqueSites[Site]),if([Site Size Cumulative] >0,1,0))Note the new dimenstion table called UniqueSitesFantastic MattAllington this is perfect, exactly what I needed! Thank you so much.
