Customers whitout current filtered year orders

[marpisa]

Hi, I have a table containing  Customers names and Year of last order

Customer Name  | Year
Customer 1           2018
Customer 1           2019
Customer2            2018
Customer 2           2019
Customer 3           2018

I Have a filter slicer with years in my Report.

Now I want shows only customes whitout slicer Year orders.

So, if the filter show 

2019

my result will be only

Customer 3

Otherwhise the filter shows

2018

The list will be empty

In Sql my query will be:

SELECT Customer 
FROM Table
WHERE Year <> 2019

How I build the measure in PowerBi?

Thanks

MArco

[v-piga-msft · Accepted solution]

Hi @marpisa ,

To get your desired output in Power BI, please follow the steps below.

1. Create the calculated table with the formula below.

slicer = VALUES(Table2[Year])

2. Create the measure like this.

Measure = 
VAR curr =
    SELECTEDVALUE ( Table2[Customer Name] )
VAR _string =
    CONCATENATEX (
        FILTER ( ALLSELECTED ( Table2 ), Table2[Year] = SELECTEDVALUE ( slicer[Year] ) ),
        Table2[Customer Name],
        ","
    )
VAR searchvalue =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table2[Customer Name] ),
            SEARCH ( curr, _string, 1, -1 ) > 0
        )
    )
RETURN
    IF ( searchvalue > 0, 0, 1 )

3. Drag this measure in visual level filter and set like below.

Then you could create the year slicer based on the year column in the calculated table.

More details, please refer to my attached pbix file.

Hope this can help you!

Best  Regards,

Cherry