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