Compare one value from a measure to all the rest using ALLEXCEPT

[Anonymous]

Hello Power BI community!

This might be a repeating post but I can't make this to work. I have a simple data of restaurants reservations. I have a task to compare one restaurant vs. all others on a monthly basis. So, basically I need to count the reservations per selected restaurant on a slicer and then subtract it from all reservations. But I allso need to take into consideration filter context of a date hierarchy in my eigher chart or a matrix. So I need to use ALLEXCEPT to retain the date filter. 

Below is my DAX:

ComparedResult =

VAR SelectedVenue = SELECTEDVALUE(Resy[Venue_Name__c])

VAR AllOtherReservations = CALCULATE

(

COUNT(Resy[Name]),

ALLEXCEPT(Resy, Resy[Reservation Date].[Date]),

Resy[Venue_Name__c] <> SelectedVenue

)

RETURN

AllOtherReservations

Here is what I have on a matrix. CompareResult is the DAX above. ReservationCount is another measure that is sliced by a restaurant. So it works but the DAX above doesn't. Any help will be appreciated.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your measure and table screen, the matrix visual is based on year and month but the measure allexcept() uses date, maybe change it like this:

ComparedResult = 
VAR SelectedVenue =
    SELECTEDVALUE ( 'Resy'[Venue_Name_c] )
VAR AllOtherReservations =
    CALCULATE (
        COUNT ( Resy[Venue_Name_c] ),
        ALLEXCEPT ( Resy, Resy[Reservation Date].[Month] ),
        Resy[Venue_Name_c] <> SelectedVenue
    )
RETURN
IF( AllOtherReservations <> BLANK(), AllOtherReservations, 0 )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yingjie Li, thank you, it works for the most part. The only issue is that now I see data for the rest of the year in 2020 and first two months in 2019 where there was no data. It stays constant when I click different restaurants. I checked my data and the latest is from May 2020. I do not understand where these future months came from. Since I have the Year in a hierarchy, and I do this in DAX, can that be a reason?

ALLEXCEPT ( Resy, Resy[Reservation Date].[Month] ),

[Anonymous]

Yingjie Li, I am accepting your reply as a solution. I found a way to do both Year and Month by using a Year Month column from a Calerndar table I have that is joined to my Resy table. 

Thank you for your help!

Stan

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Anonymous]

Yes, sure. I replaced the venue name with a letter. 

Resy-0000000011	q	3/16/2019 2:00
Resy-0000000012	q	3/16/2019 2:00
Resy-0000000013	k	3/15/2019 16:30
Resy-0000000014	k	3/15/2019 16:45
Resy-0000000015	k	3/15/2019 16:45
Resy-0000000016	k	3/15/2019 16:30
Resy-0000000017	k	3/15/2019 17:15
Resy-0000000018	k	3/15/2019 17:30
Resy-0000000019	k	3/15/2019 19:00
Resy-0000000020	w	3/15/2019 17:00
Resy-0000000021	w	3/15/2019 17:45
Resy-0000000022	w	3/15/2019 17:45
Resy-0000000023	w	3/15/2019 18:00
Resy-0000000024	w	3/15/2019 18:15
Resy-0000000025	w	3/15/2019 22:00
Resy-0000000026	w	3/15/2019 22:30
Resy-0000000027	w	3/15/2019 23:45
Resy-0000000028	w	3/16/2019 0:00
Resy-0000000029	w	3/16/2019 1:30
Resy-0000000030	w	3/16/2019 1:45
Resy-0000000031	e	3/15/2019 16:45
Resy-0000000032	e	3/15/2019 17:00
Resy-0000000033	e	3/15/2019 18:30
Resy-0000000034	e	3/15/2019 22:00
Resy-0000000035	e	3/15/2019 23:00
Resy-0000000036	e	3/15/2019 23:30
Resy-0000000037	e	3/16/2019 0:00
Resy-0000000038	e	3/16/2019 0:00
Resy-0000000039	e	3/16/2019 0:15
Resy-0000000040	e	3/16/2019 0:30
Resy-0000000041	e	3/16/2019 0:30
Resy-0000000042	e	3/16/2019 1:00
Resy-0000000043	h	3/15/2019 21:45
Resy-0000000044	h	/16/2019  12:00:00 AM

[Anonymous]

Sorry, forgot the headers:

Name

Venue_Name__c

ReservationDate

The last date is starting with 3 for March.