Filter based on new unique data in a range?

[Anonymous]

Hi PowerBi Community,

Need some assistance.

I am hoping to build a formula which will return only new unique values based on a column (Full Name) in a table (Append1).

So for example if I set a date slicer to 12/01/2018 through 12/31/2018 I want it to a list all Full Names which have not appeared since the beginning of my data table. (first date is 1/1/2018).  So basically this would return a list of names which have not appeared in the data set before until that point - if it was set for the full date range it should then pull every data item. 

Is this possible? My table Append1 has columns for both Date and Full Name.

Any help or pointers in the right direction would be appreciated!

Thanks in advance!

[AlB]

Hi @Anonymous

If I understand correctly you need a list of the new names that appear in the period selected by the slicer. 

Let's try this (Append1 is your table as indicated):

1. Place Append1[Date] in the slicer

2. Place Append1[Full Name] in the rows of a matrix visual

3. Create this measure:

ShowMeasure =
VAR _NamesSelectedPeriod =
    VALUES ( Append1[Full Name] )
VAR _NamesBeforeSelectedPeriod =
    CALCULATETABLE (
        VALUES ( Append1[Full Name] );
        FILTER ( ALL ( Append1 ); Append1[Date] < MIN ( Append1[Date] ) )
    )
VAR _NewNames =
    EXCEPT ( _NamesSelectedPeriod; _NamesBeforeSelectedPeriod )
VAR _IsNewName =
    SELECTEDVALUE ( Append1[Full Name] ) IN _NewNames
RETURN
    IF ( _IsNewName; 1 )

4. Place the measure in Visual level filters and select 'Show items when the value is:' --> 1 

This will show in your matrix the names that appear in the period selected in the slicer but did not appear before.

[Anonymous]

Replacing the ";" with commas works perfect as directed! Thank you!

I'm now trying to repeat this exact filter on other visuals to only pull information associated with names on the list in this Matrix - if I try to add this filter to another visual or table it does not let me apply the filter.

Basically is there a way to replicate this exact filter on other visuals/tables? For example each name is associated with a payment for a different product - can I produce a table that shows X amount received for X product from these "new" payees? My data has more information as well but these are available columns: Date, Full Name, Amount, Product 

So if we received $10,000, but only $1,000 was from the folks in the "new" list I just want to pull the $1,000.

Is this possible?

Thanks again for the help!

Edit: Basically if I could apply this as a page level filter (except the timeline slicer) then that would be perfect (or something that accomplishes something similar?)