Forum Discussion
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
3 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
- amitchandakSuper User
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