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