DAX equivalent of NOT EXISTS

[Dave2]

I'm trying to figure out how to show rows in a table that do not have corresponding values in another table, while still being able to slice my data.

I have a Sales fact table with a store dimension and a time dimension. There's a number of slicers on the store information, slicing by various properties of the store.

What I want to do is show Stores that do not have Sales reported. For the life of me, I cannot figure out how to do this.

For example, if I slice Store to only show "Category 1" and then slice the time period to only show "July 2016":

1. I can iobviously quite easily show a table of stores and sales reported.
2. But I can't seem to figure out how to show a list of all Category 1 stores that have not reported sales

I'm new to DAX, so there's likely something basic I'm missing here (I've been trying to understand row/filter contexts and when they get used, but am having trouble visualizing what's active at any given moment in the formula... let alone know where I should be manipulating them).  If I was using SQL, I'd do something like this...

SELECT * FROM Stores WHERE Category='Category 1' AND StoreId NOT IN (SELECT StoreId FROM Sales WHERE Period='201608')

So I thought maybe I could use ALLEXCEPT to somehow create a calculated table that achieves this, but have so far been very unsuccessful with understanding how to construct what I want.

Any help or guidance would be very much appreciated.

Dave

[v-haibl-msft · Accepted solution]

@Dave2

If you need to get a table back, I think you can first create a table as jahida said. I assume we have a calendar table and another two tables as below.

We can create a new table with following formula.

Table = 
GENERATE (
    FILTER ( SUMMARIZECOLUMNS ( Sales[Time] ), Sales[Time] <> BLANK () ),
    FILTER ( SUMMARIZECOLUMNS ( Stores[StoreName], Stores[Category] ), Stores[StoreName] <> BLANK () )
)

And the relationship will like below.

Then create a new column in this new table with following formula.

Sales = 
VAR ActualSales =
    LOOKUPVALUE (
        Sales[Sales],
        Sales[Time], 'Table'[Time],
        Sales[Store], 'Table'[StoreName]
    )
RETURN
( IF ( ActualSales <> BLANK (), ActualSales, 0 ) )

Drag two slicers (one for month and one for category), two tables (first one for stores have sales and second one for stores not have sales) into your canvas.

For the first table, apply a visual level filter (Sales is not 0). For the second table, apply a visual level filter (Sales is 0).

I’ve also upload my .pbix file here for reference.

Best Regards,

Herbert

[Dave2]

@jahida and @v-haibl-msft, thank you so much.  As I understand, the key concept in both is to have the store dimensions that I wish to slice on duplicated in a separate table together with a measure for the sales.

I see from Herbert's example (thank you so much for the awesome detail, you've really helped me undertstand a lot) that the generated table is the product of all stores with all times, which I'm a bit concerned will create a huge table with our real-world data (or maybe this is just the price to pay for the flexibility we want).  With jahida, there only seems to be the stores duplicated; but I'll have to go through the implementation to see whether it will work, I don't know enough yet to tell from reading.

I think this has all given me enough to tackle the problem from a fresh perspective.  I'll give it a shot and see how it goes (and comment back here).

Dave

[Dave2]

Okay, so thanks to all your help, I think I have a working solution.  (Thank you especially @v-haibl-msft, the PBIX helped me narrow down the behaviour problems I was having... lesson learned is to create a small subset of data so I can analyze what's going wrong when I have unexpected behaviour.)

I didn't end up needing a calculated table at all, I was able to achieve the result using just a measure (as @jahida suggested, since my Stores dimension is already unique) on the Stores dimension (based mostly off of Herbert's formula):

Actual Sales = 
VAR ActualSales =
    SUM(Sales[Sales])
RETURN
    ( IF ( ActualSales <> BLANK (), ActualSales, 0 ) )

Then everything worked, as long as I never referenced the Sales table directly in the visual.  If I have the visuals just use the time from the Calendar, the name and properties of the Store, and the "Actual Sales" measure.  (With Actual Sales=0 and Actual Sales<>0 on the two visuals.)

One odd thing that I don't quite yet understand is that when doing it this way, I get a blank results in the Actual Sales=0 visual that I need to filter out.  When all the slicers are on the Stores table I get a superfluous Category=(Blank) showing up, and if I have slicers on dimensions off of the Stores table then I get blanks on those dimensions as well as the Store table itself.  (Because of this I'm thinking I should flatten my snowflake schema down so it's a star schema, which I see is recommended everywhere.)  Filtering the blanks out works; I'll have to try to understand why this is happening though... I'm guessing that without the Sales fact table referenced, it's doing a full outer join on all the involved tables, and the IF/ISBLANK on the sales count is allowing these tuples to be shown.  Hopefully this kind of thinking will become easier as I work more with DAX.  😕

In any case, I have a working solution (or at least am a good part of the way there).  Thank you so much for all the help.

Dave

[Anonymous]

Generally, you are going to have a filter that's pretty easy, say...   FILTER ( DimStore, [Total Sales] = 0)

The "problem" is that measures (the only way to do a dynamic calculation) really want to return a single scalar value.  So... we need to decide something reasonable to display/use.

Say...

NumNoSaleStores = CALCULATE ( COUNTROWS(DimStore), FILTER(DimStore, [Total Sales] = 0) )

[Dave2]

@Anonymous, thanks for the suggestions.  I do need to get a table back, and not a scalar value... I was hoping that the "calculated table" in DAX would make this possible?

I think part of the problem I'm having is trying to wrangle the filter contexts; I really am struggling in my understanding since there's no easy way (that I can see) of debugging/viewing contexts at each step of the formula.  Even then, I'm not certain how to keep the context around to use as part of a separate query.

(That is, using your example I need to have the date filter to determine [Total Sales] = 0, but then need to clear the date filter to get all Stores.  I have no idea how to even start here; although if it's not possible to do that would be great to know.)

Dave

[Anonymous]

At the end of the day, what you are going to use in your visuals is table columns, and measures.  That's it.  You can use Power Query or the "New Table" functionality to add more tables... but those are always static (updated only at Refresh time).

There is no way to say    = FILTER(MyStores, Stores[State] = "CA")   someplace where the entire (filtered) table is now available to drag and drop columns onto a visual.

Your questions around adjusting the filter context... ya, that is certainly what makes DAX both powerful and tricky 🙂   But in general, I think what you are trying to do IS possible... and I suspect you may be overthinking it.  You would be surprised how much stuff "just works" 🙂

[jahida]

I would recommend first creating a calculated table which is just a distinct list of stores, let's call it Stores. Create a relationship between Stores and Sales. Then make a measure inside Stores:

CountOfSales = COUNTROWS(Sales).

If you want to show all the stores with no sales, just put the stores into a table, and add a Visual level filter for CountOfSales = 0.

Hope that helps! Might not be the most memory-efficient way but should be relatively easy to work with.

[a_mixed_life]

I believe "ISBLANK" along with "IF" might help.

[Dave2]

Hey Kris... thanks for the suggestions.  I'll try incorporating those in various places and see if it sparks any sort of "aha" moment for me (right now I still feel like I'm taking shots in the dark, and missing something fundamental in my DAX thinking).  I'll post here if I ever figure anything out.