How do I avoid duplicate values for a column ...

[StudentHomes_Pl]

How do I avoid duplicate values for a column in a specific data slice?

I’m sure this should be simple but I cannot find how to do it! I have a table “Tenancy Application” with multiple columns but the significant columns for this query are [TenancyAppID], [Tenant] and [TenancyYear].

I want a data slice that includes all columns but only displays a single occurrence of [Tenant] values. Reason being a [Tenant] could occur on one or more row due to having different [TenancyYear] value.

What do I need to add to following filter expression to achieve this?

ISNOTBLANK([Tenant])

(It would also be a bonus if I could sort the records alphabetically at the same time, although I appreciate that I can do this with my selected views)

[Aleksi_Alkio]

So… are you saying that you want to filter duplicate records away with the slice. You only want to show one record from that “group”?

[StudentHomes_Pl]

@Aleksi_Alkio Yes, basically using it to display list of tenants’ information and do not want the same tenant to appear twice.

[Aleksi_Alkio]

You’re welcome

[Aleksi_Alkio]

You would need to add a virtual column… for example with the MAXROW expression and then filter the data with a formula like [KeyColumn]=[MaxRowColumn]

[StudentHomes_Pl]

Thanks Aleksi, I’ll give it a try

[Aleksi_Alkio]

Another way could be a simple formula in initial value when you add a new record. It could be like IFS(NOT(IN([Tenant],TableName[Tenant])),1) and then you can filter it with the simple condition like [TestColumn]=1. This will work if you never delete any record from that table. The good thing is that you don’t need to calculate the MAXROW value every time when you sync the app.

[StudentHomes_Pl]

I’ve tried to create virtual column using

MAXROW(“Tenancy Application”,[TenancyAppID])

but expression builder states

“Function ‘MAXROW’ should have at least two parameters: a table name, a column name, and an optional filter expression”

[Aleksi_Alkio]

Try with MAXROW(Tenancy Application,TenancyAppID).

[StudentHomes_Pl]

Appears that I am unable to use MAXROW as this returns

“The inputs for function ‘MAX’ should be a list of numeric values”

My key column [TenancyAppID] is a UNIQUEID() value. Sadly cannot implement your other suggestion as working with live data that has already been entered.

Guess I may have to give up on this idea as already spent loads of time trying to figure out how to do it.

[Aleksi_Alkio]

Try with MAXROW(Tenancy Application,_rownumber,[TenancyAppID]=[_THISROW].[TenancyAppID])

[StudentHomes_Pl]

That one didn’t work, but managed to get it to work by using the following expressions

For virtual column MAXROW(Tenancy Application, _rownumber, [Tenant]=[_THISROW].[Tenant])

For slice filter

[TenancyAppID]=[_DuplicatesFilter]

Thanks for sticking with me on this, definitely wouldn’t have got there without your help. Once again, great support!

[TriSnatta]

if for a duplicate number format can you use this formula