Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RobbLewz
Helper II
Helper II

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 1
amitchandak
Super User
Super 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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.