How to filter based on parameter table

[janosh]

Hi all,
I'm using Excel 365 (2016). I have a parameter table (paramTbl) which I'm showing to the user as a slicer, next to the Power Pivot table. Based on user's selection, a certain measure should (or should not) apply a filter to the order's table.

Measure :=
[COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
    [COLOR=#0070FF]FIRSTNONBLANK[/COLOR][COLOR=#969696] ([/COLOR] paramTbl[param1]; paramTbl[param1] [COLOR=#969696])[/COLOR] = [COLOR=#D93124]"YTD"[/COLOR];
    [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] Orders[Amount] [COLOR=#969696])[/COLOR]; /*some other complex filter*/ ; Orders[SeasonPart] = [COLOR=#D93124]"YTD"[/COLOR] [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] Orders[Amount] [COLOR=#969696])[/COLOR]; /*some other complex filter*/ [COLOR=#969696])[/COLOR]
[COLOR=#969696])[/COLOR]

You can see from the Measure above, I use an IF statement to execute one of the two CALCULATE functions, based on the user's selection in the paramTbl. The only difference between these two CALCULATE is an additional filter at the end (SeasonPart=YTD).

There are some more, complex filters, however those are all the same in both CALCULATE versions. In order to avoid code duplication, I would like to reduce one CALCULATE and remain only one version. How can apply the last filter (SeasonPart=YTD) with correct syntax?

Thanks for your help
Regards
Janosh

 

[philiplaucpa]

You may want to use DATESTYD(), DATESQTD(), and DATESYTD() function inside your calculate table. You may need the date table in order to use them.

 

[janosh]

Thanks for your answer, philiplaucpa. Unfortunately, there is no date table in the model. My goal is to have one CALCULATE only, something like the following:

Measure :=
    [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] Orders[Amount] [COLOR=#969696])[/COLOR]; /*some other complex filter*/ ; IF ([COLOR=#0070FF]FIRSTNONBLANK[/COLOR][COLOR=#969696] ([/COLOR] paramTbl[param1]; paramTbl[param1] [COLOR=#969696])[/COLOR] = [COLOR=#D93124]"YTD"[/COLOR]; Orders[SeasonPart] = [COLOR=#D93124]"YTD"[/COLOR][COLOR=#969696]; VALUES(Orders[SeasonPart]))[/COLOR]);

However, somehow the IF is not allowed as filter in CALCULATE, or rather tables are not allowed as return value of an IF statement. Any further help?

 

[janosh]

I have tried to use SWITCH instead of IF and also tried to pre-calculate the filter in a variable, but no success so far.

This was my trial:

Meas1 :=
[COLOR=#0070FF]VAR[/COLOR] SeasonPartFilter =
    [COLOR=#0070FF]SWITCH[/COLOR][COLOR=#969696] ([/COLOR]
        [COLOR=#0070FF]FIRSTNONBLANK[/COLOR][COLOR=#969696] ([/COLOR] PrevYearCalc[PrevYear]; PrevYearCalc[PrevYear] [COLOR=#969696])[/COLOR];
        [COLOR=#D93124]"YTD"[/COLOR]; [COLOR=#0070FF]FILTER[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]VALUES[/COLOR][COLOR=#969696] ([/COLOR] Orders[SeasonPart] [COLOR=#969696])[/COLOR]; Orders[SeasonPart] = [COLOR=#D93124]"YTD"[/COLOR] [COLOR=#969696])[/COLOR];
        [COLOR=#0070FF]FILTER[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]VALUES[/COLOR][COLOR=#969696] ([/COLOR] Orders[SeasonPart] [COLOR=#969696])[/COLOR]; [COLOR=#0070FF]TRUE[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR] [COLOR=#969696])[/COLOR]
    [COLOR=#969696])[/COLOR]
[COLOR=#0070FF]RETURN[/COLOR]
    [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] Orders[Qty] [COLOR=#969696])[/COLOR]; SeasonPartFilter [COLOR=#969696])
[/COLOR]

The error message from DAX is:

Semantic Error: The true/false expression does not specify a column. Each true/false expression used as a table filter expression must refer to exactly one column.

Please note, I have omitted the complex filter part in calculate for easier reading. But I will have to add in back again, so I still would like to avoid a second CALCULATE.

 

[janosh]

Hi again...
I have found a way to solve it, using one CALCULATE() only.

Measure :=
[COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR]
    [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] Orders[Amount] [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]/* other complex filters */[/COLOR][COLOR=#969696][/COLOR];
    [COLOR=#0070FF]FILTER[/COLOR][COLOR=#969696] ([/COLOR]
        [COLOR=#0070FF]ALL[/COLOR][COLOR=#969696] ([/COLOR] Orders[SeasonPart] [COLOR=#969696])[/COLOR];
        [COLOR=#0070FF]CONTAINS[/COLOR][COLOR=#969696] ([/COLOR]
            [COLOR=#0070FF]VALUES[/COLOR][COLOR=#969696] ([/COLOR] paramTbl[param1] [COLOR=#969696])[/COLOR];
            PrevYearCalc[PrevYear]; [COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
                [COLOR=#0070FF]FIRSTNONBLANK[/COLOR][COLOR=#969696] ([/COLOR] paramTbl[param1]; paramTbl[param1] [COLOR=#969696])[/COLOR] = [COLOR=#D93124]"YTD"[/COLOR];
                Orders[SeasonPart];
                [COLOR=#D93124]"Total"[/COLOR]
            [COLOR=#969696])[/COLOR]
        [COLOR=#969696])[/COLOR]
    [COLOR=#969696])[/COLOR]
[COLOR=#969696])[/COLOR]

Using FILTER(... CONTAINS) I could establish a ad-hoc relationship between the parameter table as well as the Orders table. As the I have the option "Total" in the parameter table, but there is no such value in the Orders table, I had to add the above mentioned IF() statement which overrides the Order's table value to "Total" in case user selected "Total" in the parameter table. The effect is: If user selects "YTD", only the records with SeasonPart="YTD" are considered from Orders table. If user selects "Total", all records from Orders table are considered.

Regards,
janosh