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, I am trying to do a monthly and weekly view on currently open orders. I am using treatas so I can toggle between monthly and weekly view.
eg. Total_amount=
SWITCH(SELECTEDVALUE(Slicer[Month_Week_Select],1),
1, Calculate(sum([Amount]),TREATAS(VALUES(SLICER[Month_Week_Date]),'Sales Table'[Monthly_Date])),
2,Calculate(sum([Amount]),TREATAS(VALUES(SLICER[Month_Week_Date]),'Sales Table'[Week_date])))
Suppose there is another column named Status in Sales Table that shows whether orders are active or inactive. Is it possible to apply a filter?
eg. Total_amount=
SWITCH(SELECTEDVALUE(Slicer[Month_Week_Select],1),
1,
Calculate(sum([Amount]),TREATAS(VALUES(SLICER[Month_Week_date]),'Sales Table'[Monthly_Date])),
2,
Calculate(sum([Amount]),TREATAS(VALUES(SLICER[Month_Week_Date]),'Sales Table'[Week_date])
,Filter(Sales Table,Sales Table[Status]="Active")))
I tried the above but doesn't work. I am just wondering if it can be done in anyway? Sorry I am not able to provide the file directly.
You can add the filter condition into each CALCULATE, like
Total_amount =
SWITCH (
SELECTEDVALUE ( Slicer[Month_Week_Select], 1 ),
1,
CALCULATE (
SUM ( [Amount] ),
TREATAS ( VALUES ( SLICER[Month_Week_Date] ), 'Sales Table'[Monthly_Date] ),
'Sales Table'[Status] = "Active"
),
2,
CALCULATE (
SUM ( [Amount] ),
TREATAS ( VALUES ( SLICER[Month_Week_Date] ), 'Sales Table'[Week_date] ),
'Sales Table'[Status] = "Active"
)
)
You might want to look into field parameters, introduced in a recent update. they're an excellent method for allowing the user to choose which metrics they see.
Thanks for the answer! Regarding field parameters, if I am to do a similar task, how would I be able to add filters within the dax code generated by field parameters? eg. I want to have two total amount variables with the code you have written, one for the current week/month, one for the previous week/month, and do a week over week/month over month calculation. Apologies if it's unrelated to the thread.
To use field parameters you would create 2 separate measures using the different CALCULATE statements from the SWITCH. Add both measures to the field parameter, and then use the field parameter in all your visuals. The user picks which they want to see from a slicer.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |