cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Testing how Filter works

Hi @gvg,

 

No because you are summing the total sales the SalesX is a column to filter out the information you need it's not included in the calculation as a variable.

 

To make it that way you should have the temporary table calculated as a variable and then use it on the SUM.

 

Regards,

MFelix



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

Proud to be a Datanaut!




gvg Member
Member

Re: Testing how Filter works

Ok, good to know. Thanks @MFelix !

gvg Member
Member

Re: Testing how Filter works

Hello @MFelix,

 

This what you showed is a very useful DAX technique which I did not know before and I'd like to get deeper into it. If I, for example, have many more fields in Table1, how can I filter on some other field in Table1 that is not summarized (i.e. not included in SUMMARIZE function)? When I try to do it, Power BI would not allow to enter any field, that is not included in SUMMARIZE function. I am looking to do something like this :

 

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "salesX"; SUM ( Table1[Sales] )
        );
        [salesX] < 70 && Table1[NetQuantity]> 100
    )
)

I cannot put field NetQuantity in SUMMARIZE as summarization by it will give me wrong result for SalesX.

 

gvg Member
Member

Re: Testing how Filter works

OK, found a way out. You just add another FILTER to CALCULATE:

 

Test filter =
CALCULATE (
    SUM ( Table1[Sales] );
    FILTER (
        SUMMARIZE (
            ALL ( Table1[Country]; Table1[Region]; Table1[Sales] );
            Table1[Country];
            Table1[Region];
            "salesX"; SUM ( Table1[Sales] )
        );
        [salesX] < 70 
    ),
    FILTER (Sales, Table1[NetQuantity]> 100)
)
Highlighted
Super User
Super User

Re: Testing how Filter works

HI  @gvg,

 

Sorry for not responding to you I'm travelling in bussines, small hours to respond in the forum.

 

Glad you could work it out.

 

 

Regards,

MFelix



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

Proud to be a Datanaut!