Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

11 REPLIES 11
v-juanli-msft
Community Support
Community Support

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

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.

Anonymous
Not applicable

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 )

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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!

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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.

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix,

 

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.

Anonymous
Not applicable

Any ideas, anyone?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.