Need to display count of each bucket based on max date

[Anonymous]

I have a source table, i have data available and need to display based on max date for each bucket

Col A	Colb	date	bucket
1	1	1/11/2019	1
1	1	2/11/2019	2
1	1	3/11/2019	3
1	2	1/11/2019	1
1	2	2/11/2019	2
1	2	3/11/2019	3
1	2	4/11/2019	4
1	3	1/11/2019	1
1	3	2/11/2019	2
1	3	3/11/2019	3
1	3	4/11/2019	4
2	4	1/11/2019	1
2	4	2/11/2019	2
2	6	1/11/2019	1
2	6	2/11/2019	2
2	6	3/11/2019	3
3	7	1/11/2019	1

Need to display 

Col 1	Bucket 1	Bucket2	Bucket 3	Bucket 4
1			1	2
2		1	1
3	1

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @Anonymous ,

We can create a measure using following formula based on your describion.

Count = 
VAR Bucket =
    MAX ( 'Table'[bucket] )
VAR ColA =
    MAX ( 'Table'[Col A] )
VAR MaxDate =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[bucket] = Bucket )
    )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Col A] = ColA ),
                [Colb],
                "Max", MAX ( 'Table'[date] )
            ),
            [Max] = MaxDate
        )
    )

Then we can put it into the Matrix Visual to meet your requirement.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft 

Thanks a lot for your time in exploring, am still trying for the options to display. 

To Give more precise am giving some sample data 

serial Number	Prd Num	bucket_name	trans_date
1988	3.51839E+14	B1	2/18/2019 0:00
1988	3.51839E+14	B2	9/27/2019 0:00
1988	3.51839E+14	B3	9/18/2019 0:00
1988	3.5482E+14	B3	10/27/2019 0:00
1988	R58M15Q60FD	B1	2/22/2019 0:00
1988	R58M15Q60FD	B2	10/1/2019 0:00
1988	R58M15Q60FD	B3	10/11/2019 0:00
5483	GG7XDNWCJF8M	B1	1/24/2019 0:00
5483	GG7XDNWCJF8M	B2	9/18/2019 0:00
3378	3.54898E+14	B1	2/18/2019 0:00
3378	3.54898E+14	B2	9/15/2019 0:00
3378	3.54898E+14	B3	9/25/2019 0:00
3379	3.53341E+14	B1	3/1/2019 0:00

For each Serial, I have multiple Prd Num under various buckets..along with Date

I need to find the count of the number of Prd Num for each bucket based on Max(Date)

Then display the counts of each Prd Num on each bucket, on top should display the total count of each Bucket. 

Display Total	1	2	3	0
serial num	B1	B2	B3	B4
1988		1	2
3378			1
3379	1
5483		1

Please help me to reach this, Thanks A lot your time and support 

[v-lid-msft]

Hi @Anonymous ,

Sorry for late reply, we can create the following measure and use it in the matrix visual to meet you requirement:

count = 
VAR t =
    ADDCOLUMNS (
        GROUPBY ( ALLSELECTED ( 'Table' ), 'Table'[serial Number], 'Table'[Prd Num] ),
        "Bucket", CALCULATE (
            MAX ( 'Table'[bucket_name] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[serial Number] = EARLIER ( 'Table'[serial Number] )
                    && 'Table'[Prd Num] = EARLIER ( 'Table'[Prd Num] )
                    && 'Table'[trans_date]
                        = CALCULATE (
                            MAX ( 'Table'[trans_date] ),
                            FILTER (
                                ALLSELECTED ( 'Table' ),
                                'Table'[serial Number] = EARLIER ( 'Table'[serial Number] )
                                    && 'Table'[Prd Num] = EARLIER ( 'Table'[Prd Num] )
                            )
                        )
            )
        )
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            [Bucket] IN FILTERS ( 'Table'[bucket_name] )
                && [serial Number] IN FILTERS ( 'Table'[serial Number] )
        )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft ,

Thanks for the quick reply, am facing few errors while am applying the logic it is due to all my columns data type as Text 

Since where ever we are using EARLIER Function it's erroring out, 

As part of the solution, you have calculated count for all buckets and placed in Matrix, whereas I have to display the number of Prd Num on top as a scorecard. 

Really appreciate your help 

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

[v-lid-msft]

Hi @Anonymous ,

Based on my test, this formula should work when all the column is text, try to create measure instead of column, please put the error message after delete all confidential information if you still occor errors.

"display the number of Prd Num on top as a scorecard. " , do you want to put the total above the column header or put them into another visual?

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @sturlaws @v-lid-msft 

Possible please send me the reply, 

[sturlaws]

Hi @Anonymous,

what is it that you need to display the max of? For each date, show the max of Col A?

Or, show the value of Col A at max date for the Col B-values?

Cheers,
Sturla

[Anonymous]

Count of Colb for a max of date column for relevant bucket