how to create a summary table

[Anonymous]

i have data as below

PK	batch	status
1	1	progress
1	2	comp
1	3	comp
1	4	comp
1	5	progress
2	1	progress
2	2	progress
2	3	null
3	1	progress
3	2	progress
3	3	progress
4	1	progress
4	2	progress
4	3	progress
4	4	progress
4	5	progress
4	6	progress

I would like to filter each PK for max batch and create an output as below

col1 has 3 because it is concerned with status progress and null both

col2 has 2 because it is concerned with status progress only

col3 has 1 because it is concenred with status null only

col1	col2	col1
3	2	1

Please provide suggestions/inputs. I am a new user

[Icey]

Hi @Anonymous ,

My understanding is this:

1. You want to filter the max batch of each PK. The result is below:

PK	batch	status
1	5	progress
2	3	null
3	3	progress
4	6	progress

2. Count the number of progress, null, progress or null. The result is below:

progress / null	progress	null
4	3	1

If my understanding is right, try this:

1. Enter data to create a seperate status table and sort [status] column by [order] column.

2. Create a measure:

Count Measure = 
VAR t =
    ADDCOLUMNS (
        'Table',
        "MaxBatch", CALCULATE ( MAX ( 'Table'[batch] ), ALLEXCEPT ( 'Table', 'Table'[PK] ) )
    )
VAR t2 =
    FILTER ( t, [batch] = [MaxBatch] )
RETURN
    SWITCH (
        MAX ( 'status'[status] ),
        "progress / null", COUNTROWS ( FILTER ( t2, [status] = "progress" || [status] = "null" ) ),
        "progress", COUNTROWS ( FILTER ( t2, [status] = "progress" ) ),
        "null", COUNTROWS ( FILTER ( t2, [status] = "null" ) )
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[Anonymous]

Could you explain the code? Mainly the return part?

I understand that in the table Table, you are creating a column MaxBatch and then filtering Table. 

how did you create the Status table?

[Icey · Accepted solution]

Hi @Anonymous ,

Well, the code above return for your understanding is correct. Before I explain the return part of the code, you may need to understand the SWITCH function first.

The return part is to choose from three different situations:

If the 'status'[status] column is equal to "progress / null", then it counts the situation of [status] = "progress" or [status] = "null" in the t2 table.

If the 'status'[status] column is equal to "progress ", then it counts the situation of [status] = "progress" in the t2 table.

If the 'status'[status] column is equal to "null", then it counts the situation of [status] = "null" in the t2 table.

I use Enter data to create the status table. And I created the status table to display my measures.

Best Regards,

Icey

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