Forum Discussion

MarkSL's avatar
MarkSL
Icon for Helper V rankHelper 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

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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's avatar
      MarkSL
      Icon for Helper V rankHelper V

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee

        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,