Showing unique value in Visual without removing duplicates in the table

[Anonymous]

Hi,

Can anyone help me? I have a huge databases in my server that links to power BI data table. Is there a way that I can filter to just show the unique value only in my data report (visual) without removing duplicates in my data table. Because I do not want to remove the duplicates from data table.

Thank you

Mike

[nickyvv]

Hi @Anonymous,

not sure if this is what you are looking for, but if you choose a Slicer on your report, it will by default show the unique values in your report, without doing anything to the underlying data.

Does that solve your problem?

Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV

[Anonymous]

Hi Nicky,

Thank you for your suggestion. Unfortunately it doesn't solve my problem.

It still shows duplicate name. Please see below ( I didn't use real data or name).

If you see below, how to make Heike Voth shows only once without removing duplicates from the table.

[nickyvv]

@Anonymous, that clears things up a bit.

So you want to show only values in the table visual where Number of Inspections is not blank for example? Is that correct?

Then you can filter the table visual for that exact value. You can filter by a column value not being blank, that only shows you the 2nd row from the table, and will work for all the values.

I put an example together that shows what I mean:

Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV

[Anonymous]

Hi Nicky,

Thank you again for your willingness to help. It is actually more complicated than that. Because I purposely duplicated every single names in the table. By doing that I will get every single employee name show up in the data visual.

So I would know who hasn't done the inspection yet.

Please see below.

If I filter by "value is not blank", as per below illustration, I wouldn't know that Edmond Groff and Eleanore haven't done their inspection yet. At the same time, I don't want Heike Voth/Kris Marzano shows up twice. 

Thanks

[dax]

Hi @Anonymous ,

You could try to create a calculated column like below

Column = RANKX(FILTER('Table','Table'[name]=EARLIER('Table'[name])),'Table'[amount],,DESC,Dense)

Then  add this in filter and set filter like below

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @Anonymous ,

I think the reason is in that the data is not being summarised correctly. I guess you might switch it off for numerical (and potentially date) as it was resulting in empty rows filtering off in the output visual. Can you please add it back (right-click no the field name in the visual "values" list), instead, when you create the empty records, please put 0 vs. null for the numeric fields (No of Insp, Days since). This should work - at least worked in my test case.

Kind regards,

JB

[Anonymous]

Hi JB,

Thank you for your reply. I didn't switch off for numerical.  I also put zero for the empty record as suggested. It doesn't solve the problem, still shows duplicate name. One with zero and the other with the real data. Also I need to show names that shows with zero because the employee hasn't done anything yet. Please See below. I want Edmond abd Eleanore to show up. But I also want

Heike to shows up just once with the real data (not the zero).

Can you please more clear abour the filter you mentioned below? I don't really understand it.

Thanks

Mike

[Anonymous]

Hi @Anonymous ,

please refer to the pbix file via the link. I put a couple of scenarios there to demonstrate different approaches.

https://drive.google.com/file/d/1JhEj3x8cmGDep3H73i_WhTd18bm5hX2_/view?usp=sharing 

Both scenarios work without filtering.

Check ALL values on the visual to make sure all of them set to either "Sum", "Latest", "Last", etc. except the "Name".

Please let me know if you have any further questions.

Kind regards,

JB

[dax]

Hi @Anonymous , 

Did you try my suggestions? If you apply filter at calculated column, will it work or not?

Best Regards,
Zoe Zhi

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