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.
Hi guys.
I have a table and I want to count the rows of each group. The detail is that I want to filter by a specific column, not using a simple filter, but filtering so that all the rows in each group contain a specific value. This image is quite explanatory:
I know the syntax of SUMMARIZECOLUMNS, I just don't know how can I do this without using a rather stupid and costly approach in terms of processing:
TEST = SUMMARIZECOLUMNS ( service_kpi_parts_orders_importer[order_id], service_kpi_parts_orders_importer[part_number], service_kpi_parts_orders_importer[order_creation_year], service_kpi_parts_orders_importer[position], service_kpi_parts_orders_importer, "TYPES", CALCULATE ( DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ) ), "TYPES TO FILTER", CONCATENATEX(VALUES(service_kpi_parts_orders_importer[delivery_type]),service_kpi_parts_orders_importer[delivery_type],UNICHAR(10) ) )
Here I was trying to create a table and test some possibilities, but no success so far.
Can you suggest something? Something leaner and less processing-expensive?
Thanks in advance.
Cheers!
Solved! Go to Solution.
Hi @Anonymous ,
I thought that the only one you wanted to take out was the F you should redo to this:
positions = CALCULATE ( COUNTROWS ( FILTER ( SUMMARIZE ( ADDCOLUMNS ( service_kpi_parts_orders_importer; "yeardatum"; service_kpi_parts_orders_importer[year] ); service_kpi_parts_orders_importer[order_id]; service_kpi_parts_orders_importer[part_number]; [yeardatum]; service_kpi_parts_orders_importer[position]; "COUNT"; DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ); "Type_Order"; CONCATENATEX ( ALLSELECTED ( service_kpi_parts_orders_importer[delivery_type] ); service_kpi_parts_orders_importer[delivery_type]; "." ) ); [COUNT] = 1 && [Type_Order] = "S" ) ) )
The bold part of the measure is the filter you should change to get the order type you want.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous
Without creating a new table, create measures
type = CALCULATE ( DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ), ALLEXCEPT ( service_kpi_parts_orders_importer, service_kpi_parts_orders_importer[order_id] ) ) TYPES TO FILTER = CALCULATE ( CONCATENATEX ( VALUES ( service_kpi_parts_orders_importer[delivery_type] ), service_kpi_parts_orders_importer[delivery_type], UNICHAR ( 10 ) ), FILTER ( ALL ( service_kpi_parts_orders_importer ), service_kpi_parts_orders_importer[order_id] = MAX ( service_kpi_parts_orders_importer[order_id] ) ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-juanli-msft.
Thanks!
This can work but it's quite similar to what I tried. I'll add more info on how I am currently doing the actual measure and how I wanted it to work.
So now I have this measure:
positions = CALCULATE ( COUNTROWS ( SUMMARIZE ( ADDCOLUMNS ( service_kpi_parts_orders_importer, "yeardatum", YEAR ( service_kpi_parts_orders_importer[order_creation] ) ), service_kpi_parts_orders_importer[order_id], service_kpi_parts_orders_importer[part_number], [yeardatum], service_kpi_parts_orders_importer[position] ) ), service_kpi_parts_orders_importer[delivery_type] = "S" )
Where it fails to meet my requirement is here:
service_kpi_parts_orders_importer[delivery_type] = "S"
This filters the rows for "S", but I want to filter on all the rows of the group. So for this example:
I want the measure to return 1, but now it returns 2, as it takes out the "F" but still there is a "S" on the first group.
Is there a way to do this inside the SUMMARIZE on an efficient way?
Thanks again.
( @MFelix, still alive? )
Hi @Anonymous ,
How are you?
Picking up on your measure why don't you change it to:
positions = CALCULATE ( COUNTROWS ( FILTER ( SUMMARIZE ( ADDCOLUMNS ( service_kpi_parts_orders_importer; "yeardatum"; service_kpi_parts_orders_importer[year] ); service_kpi_parts_orders_importer[order_id]; service_kpi_parts_orders_importer[part_number]; [yeardatum]; service_kpi_parts_orders_importer[position]; "COUNT"; DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ) ); [COUNT] = 1 ) ) )
If you want this to be also related to your filter so if you select the S it doesn't return a result of two redo the measure to:
positions= CALCULATE ( COUNTROWS ( FILTER ( SUMMARIZE ( ADDCOLUMNS ( service_kpi_parts_orders_importer; "yeardatum"; service_kpi_parts_orders_importer[year] ); service_kpi_parts_orders_importer[order_id]; service_kpi_parts_orders_importer[part_number]; [yeardatum]; service_kpi_parts_orders_importer[position]; "COUNT"; DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ) ); [COUNT] = 1 ) ); ALL ( service_kpi_parts_orders_importer[delivery_type] ) )
Should work not really sure about performance.
Check PBIX file attach.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAlways good! New industry
Makes some sense, but then if there is a group with all "F" it will count, and that is not as intended.
Quite tricky, this one!
Hi @Anonymous ,
Didn't realize that you wanted to filter out the F type try this measure.
Again this probably can be improved in terms of performance.
positions = CALCULATE ( COUNTROWS ( FILTER ( SUMMARIZE ( ADDCOLUMNS ( service_kpi_parts_orders_importer; "yeardatum"; service_kpi_parts_orders_importer[year] ); service_kpi_parts_orders_importer[order_id]; service_kpi_parts_orders_importer[part_number]; [yeardatum]; service_kpi_parts_orders_importer[position]; "COUNT"; DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ); "Type_Order"; CONCATENATEX ( ALLSELECTED ( service_kpi_parts_orders_importer[delivery_type] ); service_kpi_parts_orders_importer[delivery_type]; "." ) ); [COUNT] = 1 && [Type_Order] <> "F" ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Thanks but still the same. The first group, which has some "F" is still counted...
If a group is not all "S" it should be removed, and a simple filter is not enough for this.
Hi @Anonymous ,
Based on the data you send out the result is giving me the rigth result, can you share some sample data that as the full setup of the table?
Maybe trough private message, if information sensitive.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi again @MFelix
There's too much data on the real file and it is easier to use this example, I think.
I'll leave some remarks using the file you previously sent, it's clearer this way:
Thanks for the 1000th time
Hi @Anonymous ,
I thought that the only one you wanted to take out was the F you should redo to this:
positions = CALCULATE ( COUNTROWS ( FILTER ( SUMMARIZE ( ADDCOLUMNS ( service_kpi_parts_orders_importer; "yeardatum"; service_kpi_parts_orders_importer[year] ); service_kpi_parts_orders_importer[order_id]; service_kpi_parts_orders_importer[part_number]; [yeardatum]; service_kpi_parts_orders_importer[position]; "COUNT"; DISTINCTCOUNT ( service_kpi_parts_orders_importer[delivery_type] ); "Type_Order"; CONCATENATEX ( ALLSELECTED ( service_kpi_parts_orders_importer[delivery_type] ); service_kpi_parts_orders_importer[delivery_type]; "." ) ); [COUNT] = 1 && [Type_Order] = "S" ) ) )
The bold part of the measure is the filter you should change to get the order type you want.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Thank Saviour, I didn't expect it to be so simple, the ALLSELECTED there does an interesting job.
Thanks a lot again!
Fica bem! Abraço.
Any ideas, anyone?
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |