Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
GMen
Frequent Visitor

Wrong result with some Slicers

Hi PowerBI community,

I'm relatively new to PowerBI and have been working through some exercises to get familiar with the tool. I started with the standard financial example (financial.xlsx) and, as a second exercise, attempted to create a report using the Top 250 Series overview from IMDB.

In the process, I utilized the Query Editor to adjust the format of some data. In my PowerBI report, I created a table with relevant information and added five slicers:

  1. Start Year
  2. Minimum Score
  3. Minimum Number of User Ratings
  4. Normal or Mini Series
  5. Rating

IMDB.png
For the first three slicers, I created additional tables with the respective values and established relations between these new tables and the main table.

  • JaartallenLijst = GENERATESERIES(min(Series[Start]), max(Series[Start]), 1)
  • ScoreLijst = GENERATESERIES(min(Series[Score]), max(Series[Score]), .1)
  • AantalStemmen = UNION(
    SELECTCOLUMNS(GENERATESERIES(0, 900, 100), "Aantal", [Value]),
    SELECTCOLUMNS(GENERATESERIES(1000, 9000, 1000), "Aantal", [Value]),
    SELECTCOLUMNS(GENERATESERIES(10000, 90000, 10000), "Aantal", [Value]),
    SELECTCOLUMNS(GENERATESERIES(100000, 1000000, 100000), "Aantal", [Value])
)
  

relations.png
While the Year slicer works perfectly, the Minimum Score and Minimum Number of User Ratings slicers are producing incorrect or no results.

MinimumRatings.pngMinimumScore.png

I attempted to troubleshoot by removing unnecessary slicers, leaving only the main table and one of the problematic slicers, but the issue persists.

Another observation is that, even though the table with minimum numbers of user ratings only contains round numbers (e.g., 100, 200, ..., 1000, 1100, ..., 1000000), the slicer displays numbers in between. I'm unsure if this is related to my primary issue.

Any insights or guidance on resolving these slicer-related problems would be greatly appreciated.

Thanks in advance for your assistance.

15 REPLIES 15
Nithinr
Resolver III
Resolver III

The way you have created AantalStemmen is incorrect it takes only rounded numbers and when you join(relationship) it with main table the join is only true for rounded numbers and others will get ignored, when you use that column as filter. join only takes exact match. @GMen 

10000 is huge gap missing too many values.

try this instead.

 

AantalStemmen =
UNION(
    SELECTCOLUMNS(GENERATESERIES(0, 900, 100), "Aantal", [Value]),
    SELECTCOLUMNS(GENERATESERIES(1000, 9000, 1000), "Aantal", [Value]),
    SELECTCOLUMNS(GENERATESERIES(10000, 90000, 1000), "Aantal", [Value]),
    SELECTCOLUMNS(GENERATESERIES(100000, 1000000, 1000), "Aantal", [Value])
)
GMen
Frequent Visitor

Thanks for your response. Although it doesn't explain why the score slicer isn't working.
The idea behind using round numbers was to create a more usable slicer. With small numbers, the gap between them is small, and the bigger the number, the bigger the gap. The filter in the slicer is set to "Bigger than," so I hoped to capture all those results. As I mentioned in my initial post, the slicer is displaying all the numbers instead of only the round ones. If this is impossible, the use of an extra table is redundant. In that case, I can use the field from the original table (and that's working fine).

No   SELECTCOLUMNS(GENERATESERIES(100009000010000), "Aantal"[Value]),  this is 10000 range between 10000 to 90000, and this GENERATESERIES(100000, 1000000, 100000), "Aantal", [Value]) is 100000, this will remove all the values which are 257000, 271000 when you use slicer on AantalStemmen, without AantalStemmen slicer your Score will work fine, but with it it will not due to unavailability of values in it, if no value no relation. 

GMen
Frequent Visitor

OK, thanks for the help.
But even when I remove the "AantalStemmen"-table (and slicer), the Score-slicer gives a wrong result.

GMen_0-1703680876897.png

 

Sorry @GMen   Jumped the gun too early, looks like some issue with generateseries , When I use distinct  its working fine as expected. Some reason generate series is not refreshing in relationship.

ScoreLijsg = DISTINCT(Series[Score])

looks like there is known issue with generate series be cautious while using it for floating point values.  

Generating a series of numbers in DAX - SQLBI

GMen
Frequent Visitor

I thought you had found the solution for the score slicer because I remembered some of the numbers were ...000000000000001 (and I changed the number of decimals to one). However, I have modified the DAX to:

ScoreLijst = SELECTCOLUMNS ( GENERATESERIES(min(Series[Score]), max(Series[Score]), .1) , "Value", CURRENCY ( [Value] ))
Yet, the issue remains the same.

modify dax to this 

ScoreLijsg = DISTINCT(Series[Score])

One thing to keep in mind - anything above 1000 distinct values will cause forced sampling.

lbendlin
Super User
Super User

change the first and third joins to single directional, from the slicer table to the fact table.

GMen
Frequent Visitor

Thanks for your response.
Initially, they were unidirectional, and I modified them to check if it would yield a better result.
However, that wasn't the issue.

Can you share a sample file to review

It is not in pbix format

Strange. Second try:
IMDB-Series - origineel.pbix

GMen_0-1703659606957.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.