Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |