cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Super User

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``````

Proud to be a Super User!

3 REPLIES 3
Super User

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``````

Proud to be a Super User!

Frequent Visitor

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.

Super User

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.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors