AGGREGATION - How to filter on dates with a dynamic group by which doesnt have date in aggregation

[Anonymous]

Hello everyone, 

Dataset is below:

Location	dates	count
x	1/25/2019	1
x	3/11/2019	1
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
x	1	INT1
y	2	INT2
a	1	INT1

Final output that needs to showed in report/chart
Custom calculation column	Count of custom cal column
INT1	2
INT2	1

if the user selects date slicer with range:- 01/01/2019 - 03/11/2019

we want to aggregate records for that selection and group the counts like the process below:

Count grouped without date
Location	count	Customcalclation column
x	2	INT2
y	3	INT3
a	1	INT1

Final output that needs to showed in report/chart
Custom calculatuon column	Count of custom calculation column
INT1	1
INT2	1
INT3	1

We wanted to show the final output chart grouped dynamically with respect to the slicer values. Is this possible in power bi. What is the way to go about if it is possible. I looked into summarize and calculated tables  as well as the aggregation feature (group by in power query) but not been able to effectively move forward. Any ideas or if anyone has done this type of grouping/aggregation?

@Greg_Deckler reposted with more detail explanation

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If I got it correctly, you can follow these steps to try:

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

Here is my sample file hope to help you, please try it: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjl 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? I realized countrows wont work if i want to base this logic on a different column called "impressions" which is not a count. Impressions have values from 0-1.

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

[Anonymous]

Hi @v-yingjl This worked great, I have another requirement where now I have to aggregate the "Count of custom measure" ,  because I need to subtract these counts from another value

INT        |  Count of Custom Measure

INT 1    |   2

INT 2    |    1

Result:

Count of Customer Measure

3

How would you take the sum of all the INT 1 through INT 11 into one row/result instead of how we had it seperated it by rows?

[Anonymous]

As you can see, I tried to subtract INT0 with the custom measure we created to grab INT 1 -11 but because of the row context it keeps the subtraction each row when I only want INT0 to be one value which is the "INT0 based on single or multi select" - CountofCustomMeasure @v-yingjl 

[Anonymous]

This is exactly what we needed. Thank you, it works perfectly! @v-yingjl 

[Greg_Deckler]

Very difficult to tell. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490