cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Customers whitout current filtered year orders

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

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @marpisa ,

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.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resident Rockstar

Hi @marpisa ,

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.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.