Treatas then filter in DAX

[ianrubbish]

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.

[johnt75]

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.

[ianrubbish]

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.

[johnt75]

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.