Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !!
Solved! Go to Solution.
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!
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!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
106 | |
88 | |
74 | |
69 |
User | Count |
---|---|
123 | |
112 | |
95 | |
83 | |
73 |