Forum Discussion
how to create a summary table
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
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.
3 Replies
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.
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.