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.
hi all,
what i want to achieve is that in Matrix, it could calculate previous week amount smartly according to the field that i put in row section: e.g.
when i put country in Rows, it shows
and if i put product in Rows, it will dynamtically change to
I am able to get previous week amount by the following DAX, however the problem is, because i sepecify Country in the DAX, if i want to calculate product, I would need to create another measure for it. What I want is just 1 measure to achieve this dynamic change, is it possible?
@halskwibskc Maybe:
PreviousWeekPaidImpr =
var currentweeknum = SELECTEDVALUE(bq[weeknum cloumn])
var country = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[country]),bq[weeknum cloumn]= currentweeknum -1))
var product = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[product]),bq[weeknum cloumn]= currentweeknum -1))
RETURN
IF(ISINSCOPE('bq'[country]),country,product)
hi @Greg_Deckler thanks for the quick reply.
there are acutally more than 2 fields that i want to change in the Rows section. Apart from country and products which are already mentioned, there are also sizes, colours and clients etc.
In this case, the IF function would not be able to cover all the scenario.
Do you have a new solution for it?
@halskwibskc You could do a nested IF statement but a SWITCH TRUE would be better, like below. Also, are you using a Field Parameter or no?
PreviousWeekPaidImpr =
var currentweeknum = SELECTEDVALUE(bq[weeknum cloumn])
var country = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[country]),bq[weeknum cloumn]= currentweeknum -1))
var product = CALCULATE([Paid Impr],FILTER(ALLEXCEPT(bq,bq[product]),bq[weeknum cloumn]= currentweeknum -1))
RETURN
SWITCH(TRUE(),
ISINSCOPE('bq'[country]),country,
ISINSCOPE('bq'[product]),product,
// more ISINSCOPE here
)
hi @Greg_Deckler thanks for the reply! I tired the swtich to calcaulate the difference between this week and previous week. However, the issue with swtich is that the sum total of the difference is missing if i use swtich. Do you have a way to work around this? I need the total number for futhere calculation steps.
hi @Greg_Deckler in case you missed this, I'm tagging here again. it's highly appreciated if you have a solution for this, thanks!
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.