How to dynamic change the filter in Power BI DAX based on a selected filter

0
votes

I have a table that people use in a filter and select multiple items from the for example the values in this table are US Dollar Canadian Dollar Category 1 Category 2

The users will select US Dollars and Calendar Year, which I need to affect a 2 DAX measures. The first measure should be the sum 1 of 2 different columns, something similar to

Currency Amount = CALCULATE(
    if(SELECTEDVALUE('Filter'[Description])="USD",
        Sum(Test[USD Amount]),
        Sum(Test[CD Amount])
))

Then the second measure should be something similar to the below, but what is below doesn't work and I'm fairly certain there is a better way to write this overall:

Currency Category Amount =
    if(SELECTEDVALUE('Filter'[Description])="Cat 1",
    CALCULATE(
        [Currency Amount],
        Filter(Test, Test[Category]="Cat 1")),
    CALCULATE(
        [Currency Amount],
        Filter(Test, Test[Category]="Cat 2"))
)

The problem with this is that the slicer is multi select and the Selected Value function doesn't appear to work correctly with this.

2
powerbidax
SELECTEDVALUE returns the selected value only if one value is selected, if more than one value is selected it can return a default value, or nothing. The behaviour you are asking for looks "normal" to me, and you might not need to write this kind of measures. Can you post your datamodel structure? – Giovanni Luisotto
The above is an overly simplified version of what I'm really trying to do. What I'm really doing is Cat 1/Cat 2 is Fiscal Year vs Calendar year, so the filter is applied is completely different between them. What I'm looking for is something like SelectedValue, but that works with multiple items selected and determine if what I'm looking for is 1 of the items that are selecetd. – Paul Cavacas

2 Answers

0
votes

I have come up with the following that matches what I'm looking for. If somebody can come up with a better solution I will gladly accept that instead. The main this I don't like about this is the duplication of the entire things. As I mentioned in the comment the actual filter that I would be using is much more complex, something more along the lines what is below.

Currency Category Amount = if(
    Contains(Values('Filter'[Description]), 'Filter'[Description], "Cat 1"),
    CALCULATE(
        [Currency Amount],
        Filter(Test, Test[Category]="Cat 1")),
    CALCULATE(
        [Currency Amount],
        Filter(Test, Test[Category]="Cat 2"))
)

Cat 1

FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Month In Fiscal Year] <= MAX ( 'Calendar'[Month In Fiscal Year] )
            && 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Fiscal Year] )
    )

Cat 2

FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Month In APY] <= MAX ( 'Calendar'[Month In APY] )
            && 'Calendar'[APY] = MAX ( 'Calendar'[APY] )
    )
0
votes

A way to have less code repetition is to encapsulate some of the calculations in different measures, in this way, if you need the same calculation logic you can just use the appropriate measure (the logic will be centralized in one point).

I've used the SWITCH (TRUE(), ...) to choose the appropriate measure since it allows you to have a cleaner code if the options are more than 2, and to set a default result if there are no selections or if the selected value is not valid/mapped.

Note that there might be some syntax errors since I don't have a datamodel to validate the formulas.

Currency Amount = CALCULATE(
--you probably have already replaced this with something different from SELECTEDVALUE
--If more than 2 currency exists, using SWITCH might be better (Pros above)
    if(SELECTEDVALUE('Filter'[Description])="USD", 
        Sum(Test[USD Amount]),
        Sum(Test[CD Amount])
))

Amount YTD (Fiscal Cal) =
CALCULATE(
    [Currency Amount]
    ,ALL ( 'Calendar' )
    ,'Calendar'[Month In Fiscal Year] <= MAX ( 'Calendar'[Month In Fiscal Year] )
        && 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Fiscal Year] )
)


Amount YTD (APY) =
CALCULATE(
    [Currency Amount]
    ,ALL ( 'Calendar' )
    ,'Calendar'[Month In APY] <= MAX ( 'Calendar'[Month In APY] )
        && 'Calendar'[APY] = MAX ( 'Calendar'[APY] )
)

Currency Category Amount = 
SWITCH(
    TRUE()
    ,Contains(Values('Filter'[Description]), 'Filter'[Description], "Cat 1")
        ,[Amount YTD (Fiscal Cal)]
    ,Contains(Values('Filter'[Description]), 'Filter'[Description], "Cat 1")
        ,[Amount YTD (APY)]
    ,BLANK() --If nothing match return blank (remove it if not needed)
)

I've noticed that your 'Filter' table contains several entities (currency, year/month), if viable/appropriate, splitting it into several filter tables 'FilterCurrency', 'FilterPeriod', etc... might help you by enabling the "always one selected" in slicers, Which in some cases is very useful.