cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neelofarshama
Post Partisan
Post Partisan

Slicer is filtering out wrong values

Hi,

 

I have  a table with "Device" and "Users" column shown below. The sum of users for each device are displayed. When I apply filter for Users column >= 220,000 then "Other" device disappears but then when I add the Users column to a slicer then the the table is displaying wrong values.

neelofarshama_0-1632252016059.png 

The filter Users is applied below in the filter pane and the results are accurate.

neelofarshama_1-1632253257356.png

The same slicer is applied on the page but results are not accurate as shown below it is filtering out results not >=220,000 also the mobile users number is wrong.

neelofarshama_0-1632254533369.png

 

Can someone tell me why the slicer is giving wrong values but filter is giving correct results.

Thanks in Advance

Neelofar.

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @neelofarshama 

 

I agree with @moizsherwani. Another option is to aggregate the sum of users by device into a calculated table first. Then use columns from this new table in the table visual and slicer. 

 

You can create a calculated table with below code

Table 2 = SUMMARIZE('Table','Table'[Device],"Users",SUM('Table'[Users]))

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

moizsherwani
Helper V
Helper V

Hi @neelofarshama  when you are applying a filter to the table, you are actually applying a filter to the summarized view, i.e. when you filter >=220,000 the visual looks at all the aggregated data by device and filters out where it "sees" the "total" to be greater than 220,000. If on the other hand you use the "user" column as a slicer then the slicer filters the underlying data to exclue where any of the individual rows for a device is greater than 220,000. In this case your snippet shows that only rows for mobile have values (individually) >=220,000 whereas rows for other devices do not. You can also see that the total for the mobile is different to what it was when you used it as a visual filter and it is the same reason, rows where the value for users was <220,000 were excluded. Let me know if this makes sense 

 

Regards,

 

Moiz

If this post helps, please "Accept" it as Solution to help other members find it. 

Thank you @moiz for the reply it really helped but can you suggest any other way of getting the requirement with the slicer

The easiest way which is probably not the best (because you won't be able to filter the data using any other field) is to create a calculated column that has a total of the DEVICE on each row if Desktop total is 26,083,473 then it shows up in every row where the DEVICE = desktop. The way to do that would be

 

TotalUSers =
CALCULATE (
    SUM ( 'Table'[Users] ),
    FILTER ( 'Table', 'Table'[Device] = EARLIER ( 'Table'[Device] ) )
)

 

 

The better way which maybe too complex/advanced to simply explain here would be to add a "user count table" which is a disconnected table (using a GENERATESERIES) and then creating a measure for sum of users which references this disconnected table (something along the lines of)

 

UserCountTable =
GENERATESERIES ( 0, SUM ( 'Table'[Users] ), 1 )
// use this as a slicer 


FilteredDevices =
CALCULATE (
    SUM ( 'Table'[Users] ),
    FILTER (
        VALUES ( 'Table'[Device] ),
        SUM ( 'Table'[Users] ) >= MIN ( UserCountTable[Value] )
    )
)

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors