Filter list is showing duplicate values and not a distinct list in data view

[namangarg55]

I have a fact table with more than 10 million rows. In that when I am trying to filter the "Division" column in the data view for analyzing something how many rows does it have out of 10 million for one distinct division, it shows me that division multiple times within the filter option but a function of filter is to show only distinct values. Plz help.

[Ritaf1983]

Hi @namangarg55 
I'm not sure that I understood what you are trying to do.
If you want to check for your usage, not for visualization how many distinct values do you have from special division you can use a power query "Column profile option " :

1. activate it 

2. make sure that it is showing all rows and not just 1000 :

3. filter the division according to you needs

4. go to the column that you are testing and it will show you the number of unique values:

2. The second option is from "table view" :
filter the division and again go to the column that you are trying to test it's values :

 3 option , you can create a measure like:
devision_distinct = calculate ( distincount (test column), division = "abc")

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[namangarg55]

In the table view when I am using the filter option for one column it is showing multiple same values for one division. Letz say I have two distinct division in complete column naming "HCP" & "PH". once I am trying to use the filter for that column it is showing HCP HCP HCP repetitive times. Why it is so? 

[Ritaf1983]

Because you have multiple rows with this division...

[namangarg55]

But for the filter option it should be showing only the distinct values only? 

[Idrissshatila]

@namangarg55 , now I understood your point, yes true it should be distinct in the filter.

are you sure all the division names that are even the same are enterd the same, maybe one has a space  with it.

what's the type of the column?

[namangarg55]

Exactly you got my point. I have rechecked it is entered correctly with all the same values wid no spaces nothing different. Wat is the issue then... This is happening for certain columns within my fact table 

[Idrissshatila]

@namangarg55 , what's the type of the column ?

[namangarg55]

Text

[Idrissshatila]

Hello @namangarg55 ,

check this thread https://community.fabric.microsoft.com/t5/Desktop/filter-list-is-showing-duplicate-values-and-not-a-...

try removing the column in power query then add it.

[Ritaf1983]

Distinct values for division...not for others columns
if your table is like

id	division
1	HCP
2	HCP
3	HCP
4	HP

after filtering by HCP
you will see 3 rows

[namangarg55]

I mean to ask when I am dragging the filter option to filter out something it is showing HCP HCP HCP in the filter panel to filter out. Why it is showing multiple same values within filter panel? 

[Ritaf1983]

Maybe it has invisible  spaces or something, try to trim the column via power query :

[namangarg55]

I have tried using everything including trim, even rechecking the column values but issue not resolved

[Idrissshatila]

hello @namangarg55 ,

true in the main table it will show multuple because there are multiple transactions within the dame division.

to help nuild a better data model you can check the star schema data model,

check it out https://youtu.be/1CgG0t59WcM?si=kFNi3pa4oyJVvJDw