Forum Discussion

afaro's avatar
afaro
Icon for Helper III rankHelper III
2 years ago
Solved

Can one slicer filter be selectively applied while other not?

I have a fact table which is something like this:

ProjectMetricRevenueYear
AServices202022
BProduct302022
BServices402023
AProduct502023
AServices602022
BProduct702022

 

I have two measures as follows:

Total = CALCULATE(SUM(Pipeline[Revenue]))
Services Total = CALCULATE([Total], Table[Metric] = "Services")
 
I create a matrix:
I put the rows as Project and the values as Services, Services Total.
I also add two slicers: Year and Metric
 
So, when I put my year as 2023 (for example) and metric as Product,
The value of Total changes with the filter as 2023 and metric as Product. However, the Total Services gives
me the Total with the filter only as year 2023 for each project. It doesn't apply the filter of metric as Product
which should give me the total services as 0 to my understanding but it doesn't. When I change the slicer year to 2022 (let's say), the Total Services gives me Services value for the year 2022 only but changing the slicer for metric to Product doesn't
change anything to the Services Total.

I am trying to understand why this happens. My initial thought was because the initial filter context was being
reset by CALCULATE but this doesn't seem to be the case as it is still being applied to Total and Services
Total for the year slicer.

Thank you in advance.
 
" Slicer"
"DAX help"
"DAX"
Filter context
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi afaro ,

    To ensure that the slicer correctly affects and measure, you can adjust the measure to follow the slicer selection. The following are ways to modify the measure:

    Services Total =
CALCULATE (
    [Total],
    FILTER ( ALLSELECTED ( Table[Metric] ), Table[Metric] = "Services" )
)


    This measure is used to keep the slicer selection in context while still filtering for "Services". This way, if "Products" is selected in the slicer, 0 should be displayed correctly.

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your desired out put and pbix file without privacy information.

     

    Best Regards,

    Ada Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi afaro ,

    To ensure that the slicer correctly affects and measure, you can adjust the measure to follow the slicer selection. The following are ways to modify the measure:

    Services Total =
CALCULATE (
    [Total],
    FILTER ( ALLSELECTED ( Table[Metric] ), Table[Metric] = "Services" )
)


    This measure is used to keep the slicer selection in context while still filtering for "Services". This way, if "Products" is selected in the slicer, 0 should be displayed correctly.

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

    If it does not help, please provide more details with your desired out put and pbix file without privacy information.

     

    Best Regards,

    Ada Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • afaro's avatar
      afaro
      Icon for Helper III rankHelper III
      2 years ago

      This gives me blank data for this measure since it removes all the initial filter context. But this works as using ALLEXCEPT worked for my specific use. 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    If you want independent slicers you need to feed your slicers from disconnected tables.