Forum Discussion

Jdogg's avatar
Jdogg
New Member
6 months ago
Solved

Self-Filtering

I have a table from a SQL server. When I want to see specific data via a visual based on a name I select, it self-filters certain things. My table is called WidgetHistory. The column I want to pull data from is CurrentStatusID. I need to see all widgets at status ID 23.

My current formula is:

CALCULATE (

COUNTROWS(WidgetHistory), WidgetHistory [CurrentStatusID] = 23 )

The problem is it self-filters data from another column within the table named Modifiedby. If I select a name in the drop down, it shows all widgets assigned to and modified by the person selected. If any of the widgets were modified by anyone else, they don’t appear in the count in the visual.

Example. I select John Smith in my drop down. The card visual shows 396 widgets with a current status of 23. When I go to Table view, there are 398 widgets with a current status of 23 assigned to him. 396 were assigned to and modified by John Smith. The other two were assigned to John Smith but modified by Jane Doe. This happens when I select others too.

I have tried:

CALCULATE (

COUNTROWS(WidgetHistory), WidgetHistory [CurrentStatusID] = 23, ALL(WidgetHistory[Modifiedby]))

I have replaced ALL with ALL SELECTED and REMOVEFILTERS but the visual still shows 396.

I would like the visual to show how many were assigned regardless of who modified it.

 

Any help would be greatly appreciated.

Need Help
  • pcoley's avatar
    pcoley
    6 months ago

    Jdogg the report is working as expected. by default, if you select a value in a visualization, that value will filter other visualizations in the report. 
    you can change this adding an ALL or REMOVE in the CALCULATE filters:

    CALCULATE (

    COUNTROWS(WidgetHistory), ALL(WidgetHistory), WidgetHistory[CurrentStatusID] = 23)

    Or changing the interaction between visualizations:

     i hope this help, if so please mark as a solution. kudos are welcome. 

     

10 Replies

  • Shravan133's avatar
    Shravan133
    Super User
    6 months ago

    try this: 

     

    Widgets at Status 23 :=
    CALCULATE (
    DISTINCTCOUNT ( WidgetHistory[WidgetID] ),
    WidgetHistory[CurrentStatusID] = 23,
    REMOVEFILTERS ( WidgetHistory[ModifiedBy] )
    )

    Why the table visual shows 398 but the card shows 396

    • Table visual shows distinct widgets

    • Card is counting filtered history rows

    • Two widgets disappear because their latest row was modified by someone else



    • Jdogg's avatar
      Jdogg
      New Member
      6 months ago

      DISTINCTCOUNT gives a value of 1. COUNT gives the value of 396

  • danextian's avatar
    danextian
    Super User
    6 months ago

    Hi Jdogg 

     

    CALCULATE ( COUNTROWS ( WidgetHistory ), WidgetHistory[CurrentStatusID] = 23 )

     

    The formula above is internally written as 

    CALCULATE (
    COUNTROWS ( WidgetHistory ),
    FILTER (
        ALL ( WidgetHistory[CurrentStatusID] ),
        WidgetHistory[CurrentStatusID] = 23
    )
)

     so the filter modified is applied to CurrentStatus only

     

    Try 

    CALCULATE (
    COUNTROWS ( WidgetHistory ),
    FILTER (
        ALL ( WidgetHistory ),
        WidgetHistory[CurrentStatusID] = 23
    )
)
    • Jdogg's avatar
      Jdogg
      New Member
      6 months ago

      That gives me all widgets (126,168) and the number doesn't change based on who i select in my drop down.

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    6 months ago

    Hi Jdogg 

     

    Can you try this measure

    Widgets Status 23 =

    CALCULATE (COUNTROWS (WidgetHistory),

    WidgetHistory[CurrentStatusID] = 23,

    REMOVEFILTERS(WidgetHistory[ModifiedBy]))

     

    • Jdogg's avatar
      Jdogg
      New Member
      6 months ago

      Doesn't change anything, but thank you

       

  • pcoley's avatar
    pcoley
    Super User
    6 months ago

    Jdogg the report is working as expected. by default, if you select a value in a visualization, that value will filter other visualizations in the report. 
    you can change this adding an ALL or REMOVE in the CALCULATE filters:

    CALCULATE (

    COUNTROWS(WidgetHistory), ALL(WidgetHistory), WidgetHistory[CurrentStatusID] = 23)

    Or changing the interaction between visualizations:

     i hope this help, if so please mark as a solution. kudos are welcome. 

     

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Thankyou, Shravan133, danextian , krishnakanth240 , Ashish_Mathur and pcoley for your responses.

    Hi Jdogg,

    We appreciate your inquiry through the Microsoft Fabric Community Forum.

    We would like to inquire whether have you got the chance to check the solution provided by pcoley to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

    Thank you.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 months ago

    Hi,

    Please share some dummy data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Hi Jdogg,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.