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

expressions
(StudentHomes Plymouth) #1

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) #2

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 Plymouth) #3

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

(Aleksi Alkio) #4

You’re welcome

(Aleksi Alkio) #5

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 Plymouth) #6

Thanks Aleksi, I’ll give it a try

(Aleksi Alkio) #7

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 Plymouth) #8

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) #9

Try with MAXROW(Tenancy Application,TenancyAppID).

(StudentHomes Plymouth) #10

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) #11

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

(StudentHomes Plymouth) #12

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!