Create and display dynamic NOT IN list PowerBI desktop

[NeimadB]

Hello,

I'm quite lost, so I'm asking for some help.

My schema is quite simple : one fact table and one dimension table.

I want to display Restaurant names that do not have any Sales in the period indicated in the Date slicer (TransactionDate).

Fact is about Sales in Restaurants, and the dimension table is Restaurant.

The useful columns of my datasets here are :

fact.Sales 

RestaurantId

DateId

TransactionDate

RestaurantName

dimension.Restaurants

RestaurantId

RestaurantName

dimension.Date

DateId

Date

(fact Sales also have RestaurantName, more practical)

I've made a computed table, with the following DAX formula 

ComputedRestaurantWithoutSales2 =

VAR SalesRestaurants = VALUES ( 'fact Sales'[RestaurantName] )

VAR ListRestaurants = VALUES ( 'dimension Restaurants'[RestaurantName] )

VAR NoSalesRestaurants= EXCEPT ( ListRestaurants , SalesRestaurants )

RETURN NoSalesRestaurants

This table is "OK", I can display it and the information displayed is OK, but It's not dynamic with the date slicer.

And I can't link this computed table to the facts or any dimensions, as I have an error about circular relation.

Is my method good ?

Or can you suggest anything better?

Thank you

[v-henryk-mstf · Accepted solution]

Hi @NeimadB ,

Your approach to creating a computed table using the DAX formula EXCEPT to find the restaurants that do not have any sales in the period indicated in the date slicer is a good start. However, it is not dynamic with the date slicer because you are using the VALUES function to retrieve the list of restaurant names from the fact Sales and dimension Restaurants tables, which do not take into account any filters applied to the report.

To make your computed table dynamic with the date slicer, you need to use the FILTER function to filter the fact Sales table based on the selected dates in the slicer. You can then use the DISTINCT function to get a list of distinct restaurant names from the filtered table, and use the EXCEPT function to find the restaurants that are not present in this list.

Here is an example of how you can modify your DAX formula to achieve this:

ComputedRestaurantWithoutSales =
VAR SalesRestaurants =
    DISTINCT (
        FILTER ( 'fact Sales', 'fact Sales'[TransactionDate] >= MIN ( 'Date'[Date] )
                             && 'fact Sales'[TransactionDate] <= MAX ( 'Date'[Date] )
    ),
        'fact Sales'[RestaurantName]
    )
VAR ListRestaurants = VALUES ( 'dimension Restaurants'[RestaurantName] )
VAR NoSalesRestaurants = EXCEPT ( ListRestaurants , SalesRestaurants )
RETURN NoSalesRestaurants

This formula filters the fact Sales table based on the selected dates in the Date slicer, gets a list of distinct restaurant names from the filtered table using the DISTINCT function, and then finds the restaurants that are not present in this list using the EXCEPT function.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[NeimadB]

Hello @v-henryk-mstf 

thank you for this very detailled answer, and the code below.

I replaced vales with mine in your code (below), and I tried to create a computed table with it, but I got an error on the DISTINCT function

ComputedRestaurantWithoutSales2 = 
VAR SalesRestaurants =
DISTINCT (
FILTER ( 'Sales', 'Sales'[TransactionDate] >= MIN ( 'dimension Date'[DateDate] )
&& 'Sales'[TransactionDate] <= MAX ( 'dimension Date'[DateDate] )
), 'Sales'[RestaurantName]
)
VAR ListRestaurants = VALUES ( 'dimension Restaurants'[RestaurantName] )
VAR NoSalesRestaurants = EXCEPT ( ListRestaurants , SalesRestaurants )
RETURN NoSalesRestaurants

Too much arguments for DISTINCT function. Maximal number of argument is 1.

Do you have any idea ?

[amitchandak]

@NeimadB , create these two measures

sales = countrows(Sales)

no sold = countx(values(Restaurants[RestaurantId]), if(isblank([sales]), [RestaurantId], blank()) )

isblank is not just to check blank values, It can be used to flip the behavior of a measure, which comes handy to solve the problem like customer retention easily: https://www.youtube.com/watch?v=roGE2qrp-eA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=54

[NeimadB]

Thank you.

As I understand, your solution only display the count of the Restaurant With or Without Sales, But I also want to display the Restaurant Lists, and I guess that a measure can't do the trick.