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
Super User
Super User

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. 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
Proud to be a Super User!

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] )
    )
)

 

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
Proud to be a Super User!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!