Forum Discussion

MarkSL's avatar
MarkSL
Helper V
7 years ago
Solved

SELECTEDVALUE not filtering SUMMARIZEDCOLUMNS?

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

3 Replies