cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
svalen Member
Member

SUMMARIZECOLUMNS - FILTER on all rows of each group

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:

SUMMARIZE.png

 

 

 

 

 

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Hi @svalen ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




11 REPLIES 11
Community Support Team
Community Support Team

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Hi @svalen 

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] )
    )
)

2.png

 

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.

svalen Member
Member

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

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:

SUMMARIZE.png

 

 

 

 

 

 

 

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? Smiley Very Happy )

Super User
Super User

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Hi @svalen ,

 

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

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




svalen Member
Member

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

@MFelix 

Always good! New industry Smiley Wink

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!

Super User
Super User

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Hi @svalen ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




svalen Member
Member

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

@MFelix 

 

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.

Super User
Super User

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Hi @svalen ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




svalen Member
Member

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Hi 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:

 

SUMMARIZE2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks for the 1000th time Smiley Wink

svalen Member
Member

Re: SUMMARIZECOLUMNS - FILTER on all rows of each group

Any ideas, anyone?