Forum Discussion
How to aggregate records based on date selection and count / assign values based on column value
Hi,
I had a requirement which a user helped me with here. The measures created aggregate records for that selection and group the counts.
Is it possible to refine this logic based on another column thats not for a straight forward count for
e.g. a column that has numbers? The user suggested countrows but this wont work if i want to base this logic on a different column called "impressions" which is not a count. For e.g. Impressions have values from 0-1. so 0 wont be counted.
Dataset is below:
Location | dates | Impressions |
x | 1/25/2019 | 0 |
x | 3/11/2019 | 0 |
y | 1/11/2019 | 1 |
y | 1/12/2019 | 1 |
y | 3/11/2019 | 1 |
a | 1/12/2019 | 1 |
What we are looking for:
If the user selects date slicer with range:- 01/01/2019 - 02/28/2019
We want to aggregate records for that selection and group the counts like the process below:
Count grouped without date
Location | count | Custom calclation column |
y | 2 | INT2 |
a | 1 | INT1 |
The prior solution and power bi was this:
1. Create a Date table related to Location date to use slicers to interact
2. Create first measure to get result of the first stage:
Custom measure =
"INT"
& COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Location] = SELECTEDVALUE ( 'Table'[Location] )
)
)
First stage result
3. Create a reference table include 'INT1', 'INT2', 'INT3', just enter data manually
4. Create second measure to get result of the second stage:
Count of custom measure =
VAR _int =
SELECTEDVALUE ( Test[INT] )
VAR tab =
SUMMARIZE (
DISTINCT ( 'Table'[Location] ),
'Table'[Location],
"Count", COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Location] = EARLIER ( 'Table'[Location] )
)
)
)
VAR newtab =
ADDCOLUMNS (
tab,
"Result", "INT"
& COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Location] = EARLIER ( 'Table'[Location] )
)
)
)
RETURN
COUNTROWS ( FILTER ( newtab, [Result] = _int ) )
Second stage result
This works if we are doing count but how can i improve this so its not using countrows but taking into a specific Column like impressions.
Here is my sample file hope to help you, please try it: PBIX
1 Reply
- TomMartens
Super User
Hey Anonymous ,
my data looks like this:
My measure like this:
Measure = COUNTX( FILTER('Table' , 'Table'[impressions] <> 0 ) , 1 )This allows to create a table visual like this:
Hopefully, this is what you are looking for.
Regards,
Tom