Forum Discussion

mbittel's avatar
mbittel
New Member
5 hours ago

What am I missing?

I am having trouble getting the first column(branch count) to respond to Slicer changes. Using this sample data I would like Branch Count to change from 4 to 2 when I set the "Active" Slicer to "Y".  

There are no relationships in my model, this is coming from a single data source.  

My Measure for Branch Count:

Branch Count = CALCULATE(

 COUNTROWS('SQL_EOB_Forms'),

    ALLEXCEPT('SQL_EOB_Forms','SQL_EOB_Forms'[Branch]))

Need Help

3 Replies

  • v-kathullac's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support
    4 hours ago

    Hi mbittel​ ,

    Thank you for reaching out to Microsoft Fabric Community Forum, Below are the few points which can resolve your issue. Let us know if you need any further assistance.

    • The issue is caused by the ALLEXCEPT() function in your existing measure. It preserves the Branch filter but removes filters from other columns, including the Active slicer.
    • Since your data comes from a single table (SQL_EOB_Forms), no additional relationships are required.
    • To resolve this, modify your existing measure to retain both the Branch and Active filters.
    Branch Count =
CALCULATE(
    COUNTROWS('SQL_EOB_Forms'),
    ALLEXCEPT(
        'SQL_EOB_Forms',
        'SQL_EOB_Forms'[Branch],
        'SQL_EOB_Forms'[Active]
    )
)
    • ALLEXCEPT() now preserves filters on both Branch and Active. The measure continues to count all matching records for the selected branch while respecting the Active slicer.
    • Ensure that [Active] is the exact column used in your Active slicer. If the actual column name is different, replace it accordingly.
    • Based on your sample data, the expected results are:
      • Active = All: Branch Count = 4.
      • Active = Y: Branch Count = 2.
      • Active = N: Branch Count = 2.
    • You do not need to create additional relationships or calculated tables for this solution.

    Thanks,
    Chaithanya.

  • mbittel's avatar
    mbittel
    New Member
    2 hours ago

    Thank you! I do see a count of 2 with either "Y" or "N" selected but it also shows 2 with "All" selected.  I should add that this is a Direct Query...

     

    • ctrain's avatar
      ctrain
      Icon for Advocate I rankAdvocate I
      1 hour ago

      Active appears in both the slicer and the table. 

      Adding [Active] to ALLEXCEPT preserves each table row’s Y/N filter too. That explains why “All” still gives 2: each row is counting only its own Active status.

      Assuming the count should respect all your slicers while remaining grouped by branch, replace the measure with:

      Branch Count =
VAR CurrentBranches =
    VALUES('SQL_EOB_Forms'[Branch])
RETURN
    CALCULATE(
        COUNTROWS('SQL_EOB_Forms'),
        ALLSELECTED('SQL_EOB_Forms'),
        KEEPFILTERS(CurrentBranches)
    )


      ALLSELECTED removes the filters created by the table’s individual rows while retaining slicer selections. CurrentBranches keeps the count specific to the branch on that row, including when multiple branches are displayed.