Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • 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