Forum Discussion

RobbLewz's avatar
RobbLewz
Helper II
5 years ago

Dynamically Ignore Slicer Interaction

I have pretty standard star schema relationship.

 

FactSales Table

  • DateKey
  • EmpId
  • ClientId
  • SaleTypeId
  • GBPValue

Dim Date Table,

  • Date Key
  • Year
  • Month etc..

Dim Employee Table

  • EmpId
  • Group
  • Employee Name

Dim Client Table

  • ClientId
  • ClientName
    etc

I have a matrix set out like

Rows: Group > Employee Name > Client Name

Columns: Year > Month

Values: (Variable) Reporting GBP

 

I have 3 slicers on the page. 
1 is for the user to change the SaleTypeId depending on what they want to look at.  (Sales, refunds, pending etc)

2 single select year

multi select month slicer.

 

The (Variable) Reporting GBP measure is just a switch function

 

 

     VAR _s = VALUE ( SELECTEDVALUE ('FactSales[SaleTypeId]'))
RETURN
    SWITCH ( TRUE(),
            _s = 1, SUM(....

 

 

 

For the majority of selections i want the matrix to only show for the single year and x months the user selected on the date slicer.

 

There are 2 though that when selected, I want them to ignore the date slicer and show alll years and months. (still retain the sale type id)

 

So in the switch measure it would be something like,  _s = 2, [(£All Measure)]

 

I create the [(£All Measure)] measure and use ALL() but it still only shows the 1 selected year in the column on the matrix.  

It seems to show the overall total for all years in that one year, but I really wanted to pivot the data over all the years but the date filter seems to still be applied.

 

Is this scenario possible to work?

 

1 Reply

  • RobbLewz , Not very clear. But in such cases have independent tables and use slicer value as per need

     

    example

    measure =
    var _tab = allselected('Date'[date])
    var _cli = allselected('Clinet'[Id])

    return
    calculate(countrows(FactSales ), filter(FactSales, FactSales[clinetID] in _cli && FactSales[Date] in _tab))

     

    You can use some other filter not to pass a filter