Forum Discussion

Kumkrong_K's avatar
Kumkrong_K
Frequent Visitor
2 years ago
Solved

Calculate with multiple table filter(?)

Hello, firstly I'm kinda new to this power bi, and been trying to understand it

I have 2 tables, they relate to each other using saleID with one to many relationship
the sale data has price and status for each bill with saleID as unique index
while the sale_data_detail has what products are on each bill
example of detail table is like this
saleID[1] have 2 rows, "Coach" and "Drink"
saleID[2] have only 1 row, "Coach"
saleID[3] have 3 rows, "Drink" "Coart" and "Souvenir"
so for saleID 1 2 3, the detail table will have 6 rows total

now I want to do something like this, the DAX show no error
but the sum number cannot be show in card

the number show up if the FILTER condition is only one of the two, but not both
could somebody please explain to me why? and how to achieve the result using some other mean?

Thank you in advance !!

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    Kumkrong_K,

     

    Try this measure:

     

    allCoach1 =
VAR vFilterTable =
    FILTER (
        CROSSJOIN (
            ALL ( tb_Sports_Saledata[status_document] ),
            ALL ( tb_Sports_Saledata_Details[product_group] )
        ),
        tb_Sports_Saledata[status_document] = "1"
            || tb_Sports_Saledata_Details[product_group] = "Coach"
    )
VAR vResult =
    CALCULATE (
        SUM ( tb_Sports_Saledata[total_price_all] ),
        KEEPFILTERS ( vFilterTable )
    )
RETURN
    vResult

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    Kumkrong_K,

     

    Try this measure:

     

    allCoach1 =
VAR vFilterTable =
    FILTER (
        CROSSJOIN (
            ALL ( tb_Sports_Saledata[status_document] ),
            ALL ( tb_Sports_Saledata_Details[product_group] )
        ),
        tb_Sports_Saledata[status_document] = "1"
            || tb_Sports_Saledata_Details[product_group] = "Coach"
    )
VAR vResult =
    CALCULATE (
        SUM ( tb_Sports_Saledata[total_price_all] ),
        KEEPFILTERS ( vFilterTable )
    )
RETURN
    vResult
    • Kumkrong_K's avatar
      Kumkrong_K
      Frequent Visitor
      2 years ago

      Thank you, your solution work 👍

      By the way, could you please explain what went wrong with my DAX?
      so that I won't repeat the same mistake
      I still don't understand what the error mean by saying it was a table with multiple values.

      Again, thank you.

      • DataInsights's avatar
        DataInsights
        Super User
        2 years ago

        Kumkrong_K,

         

        Glad to hear it works. The VALUES function returns a table of values, but the second argument of FILTER is evaluated in a row context and thus requires a single value.