SELECTEDVALUE not filtering SUMMARIZEDCOLUMNS?

[MarkSL]

Hi,

Please see my example datamodel: https://www.dropbox.com/s/8s0yto6laccjzh7/Budget%20Type%20Slicer.pbix?dl=0

I have two tables which contain budgets: HardwareTargets and SoftwareTargets.  They need to remain seperate as in my actual data model they are quite different.  Both tables contain a BudgetType column and a Year column. 

When the user selects a Year from the Year slicer, I want my BudgetType slicer to only show the BudgetTypes relating to the SELECTEDVALUE of Year from both fact tables.  

So for 2018 that would be: Budget, Forecast 3+9, Forecast 6+6, Forecast 9+3

But for 2019 that would only be: Budget & Forecast 3+9

I therefore created a DAX dimension table _BudgetTypes, to summarise the BudgetTypes from both targets, based on the selected year and use this in my BudgetType slicer

_BudgetTypes = 
var __year = 2019
// var __year = SELECTEDVALUE(Dates[Year])
RETURN
DISTINCT (
    UNION (
        SUMMARIZECOLUMNS (
            HardwareTargets[BudgetType]           
            ,FILTER (
                HardwareTargets,
                HardwareTargets[Year] = __year
            )
        ),
        SUMMARIZECOLUMNS (
            SoftwareTargets[BudgetType]
            ,FILTER (
                SoftwareTargets,
                SoftwareTargets[Year] = __year
            )
        )
    )
)

However, whilst the table returns the appropiate values when I hardcode the variable __year to 2018 or 2019, it does not work if I replace the hardcoded value with the SELECTEDVALUE(Dates[Year]).

Any ideas why?  Is there another solution?

Many thanks

Mark

[v-cherch-msft · Accepted solution]

Hi @MarkSL 

You create a table with the formula.But table cannot be dynamically changed by slicer.So the selectedvalue would not work for table.It could only used in dynamic measure.For your requirement,you may use BudgetType in SoftwareTargets or HardwareTargets as slicer to filter the table.

https://www.kasperonbi.com/dax-selectedvalue-function/

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

Regards,

[MarkSL]

Hi @v-cherch-msft 

Thanks for clarifying this for me. 

You say that to filter the budget tables that I can slicer on BudgetType in SoftwareTargets or HardwareTargets, however I want one slicer to slice both tables, hence why I went with the summarised Dax table.  Would your solution not require the user to have two slicers, one for each Targets table?

Thanks

Mark

[v-cherch-msft]

Hi @MarkSL 

You may try below formula to create the table:

_BudgetTypes = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( HardwareTargets, "Type", HardwareTargets[type] ,"Year",HardwareTargets[Year],"BudgetType",HardwareTargets[BudgetType]),
        SELECTCOLUMNS ( SoftwareTargets, "type", SoftwareTargets[type],"Year",SoftwareTargets[Year],"BudgetType",SoftwareTargets[BudgetType] )
    )
)

Regards,