Self-Filtering

[Jdogg]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[pcoley]

@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. 

[Ashish_Mathur]

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.

[krishnakanth240]

Hi @Jdogg 

Can you try this measure

Widgets Status 23 =

CALCULATE (COUNTROWS (WidgetHistory),

WidgetHistory[CurrentStatusID] = 23,

REMOVEFILTERS(WidgetHistory[ModifiedBy]))

[Jdogg]

Doesn't change anything, but thank you

[danextian]

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]

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

[Shravan133]

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]

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