Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarize with multiple filtering

Hi, 

 

I'm beginner in DAX and I have a problem: a table called 'DVENDAS' with this columns: [Ticket], [Agency], [ServiceDate], [Service], [Source], [Destination], [Status] and more;

 

And I'm trying to create a new table with this idea: I have to SUMMARIZE this columns above, and FILTER by [Agency] AND [Status], but filtering Agency in two terms: "SITE" OR "PORTAL"; filtering Status only for "CHANGE".

 

Table = SUMMARIZE (
    FILTER ( 'DVENDAS'; RELATED ( DVENDAS[Agency] ) = "SITE" || DVENDAS[Agency] = "PORTAL" && DVENDAS[Status] = "CHANGE" );
    DVENDAS;
    DVENDAS[Ticket];
    DVENDAS[Agency];
    DVENDAS[ServiceDate];
    DVENDAS[Service];
    DVENDAS[Source];
    DVENDAS[[Destination];
    DVENDAS[Status]

    
)

But I got error.

Please help :)

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

     

    Try this formula, please.

    Table =
CALCULATETABLE (
    SUMMARIZE (
        DVENDAS;
        DVENDAS[Ticket];
        DVENDAS[Agency];
        DVENDAS[ServiceDate];
        DVENDAS[Service];
        DVENDAS[Source];
        DVENDAS[[Destination];
        DVENDAS[Status]
    );
    FILTER (
        DVENDAS;
        DVENDAS[Agency] IN { "SITE"; "PORTAL" }
            && DVENDAS[Status] = "CHANGE"
    )
)

    Best Regards,

    Dale

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

     

    Try this formula, please.

    Table =
CALCULATETABLE (
    SUMMARIZE (
        DVENDAS;
        DVENDAS[Ticket];
        DVENDAS[Agency];
        DVENDAS[ServiceDate];
        DVENDAS[Service];
        DVENDAS[Source];
        DVENDAS[[Destination];
        DVENDAS[Status]
    );
    FILTER (
        DVENDAS;
        DVENDAS[Agency] IN { "SITE"; "PORTAL" }
            && DVENDAS[Status] = "CHANGE"
    )
)

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks a lot, v-jiascu-msft!

       

      And what if I want to filter by [Agency] equals "SITE" and "PORTAL" AND [Status] equals "CHANGE" AND [SalesType] equals "PTA"?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

         

        You can add more conditions to the filter part. Which table is [SalesType] in?

        Table =
CALCULATETABLE (
    SUMMARIZE (
        DVENDAS;
        DVENDAS[Ticket];
        DVENDAS[Agency];
        DVENDAS[ServiceDate];
        DVENDAS[Service];
        DVENDAS[Source];
        DVENDAS[Destination];
        DVENDAS[Status]
    );
    FILTER (
        DVENDAS;
        DVENDAS[Agency] IN { "SITE"; "PORTAL" }
            && DVENDAS[Status] = "CHANGE"
            && DVENDAS[SalesType] = "PTA"
    )
)

        Best Regards,

        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Done! 

      Sorry for being late, I was away.

       

      Thank you very much!