Skip to main content
cancel
Showing results for 
Search instead 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

Reply
marpisa
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
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

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!Smiley Happy

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.

View solution in original post

1 REPLY 1
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Capture.PNG

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!Smiley Happy

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors