Wrong result with some Slicers

[GMen]

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

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

)

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

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.

[Nithinr]

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]

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

[Nithinr]

No   SELECTCOLUMNS(GENERATESERIES(10000, 90000, 10000), "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]

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

[Nithinr]

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

[Nithinr]

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]

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.

[Nithinr]

modify dax to this 

ScoreLijsg = DISTINCT(Series[Score])

[lbendlin]

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

[lbendlin]

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

[GMen]

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.

[saudansari]

Can you share a sample file to review

[GMen]

https://1drv.ms/u/s!An3EKroSHyIIgZpHUFntj3SHH2yM2Q?e=mwgm4X

[saudansari]

It is not in pbix format

[GMen]

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