Compare values between (virtual) tables

DAX DAX Calculations
1

Hi all,

I need to compare two (virtual) tables to list all items bought in 2021 but not bought in 2020. I’ve managed to do that thanks to Sam’s INTERSECT & EXCEPT lesson.
The issue I’m facing is that when I add another column from a filter table (such as category ProCat1), I get blank lines showing up which is that the EXCEPT is not working.
Is that a DAX or a modelling issue?

Thank you for your assistance!

SampleReportv1.pbix (4.3 MB)

2

@Thimos,

I would take a very different approach here, just measure branching off revised versions of the
Quantity 2020 and Quantity 2021 measures. With regard to these measures, I’ve revised them as follows - generally speaking, you should aggregate on your fact tables and filter on your dimension tables. Thus, I’m filtering on the YEAR from the Date table, not the Supplies table.

Quantity 2020 = 
CALCULATE(
    SUM('Supplies'[ΠΟΣ]),
    FILTER(
        ALL( 'Date' ),
        'Date'[Calendar Year] = 2020
    )
)

Note that your Year field was formatted as text, so I had to change this to whole number to get the measure to work.

Now the measure that does most of the heavy lifting is this one:

In 2021 Not 2020 =

IF(
        AND(
            [Quantity 2021] > 0,
            OR(
                [Quantity 2020] = BLANK(),
                [Quantity 2020] = 0
            )
        ), 1, 0
    )

This just evaluates whether an item was bought in 2021 and not in 2020 (allowing for “not bought” to be a blank or a zero), and returns a 1 if both conditions are true, 0 otherwise. Then in the filter pane, you set it to only show records where this measure = 1.

image
image903×500 40.6 KB

Note that I just included this measure in the table to show you it was working properly - no need to include it in the final report visual, as long as you have the filter pane set as shown.

I hope this is helpful. Full solution file provided below.

  • Brian

P.S. Lots of issues with your data model as well, but they don’t affect the outcome of this calculation. Let me know if you want to discuss those too.

eDNA Forum - Bought in 2021 not 2020 Solution.pbix (4.3 MB)

3 Likes
3

Hi Brian,

Great solution, works fine.
It is very interesting that you used a far simpler logic to achieve that than the one in mind.

I would definitely appreciate your assistance on the data model as well. Should I wait for your feedback in this post?

BR / Thimios

4

@Thimios ,

Great – glad to hear that worked well for you.

With regard to the data model, here are the issues that I see:

  1. generally, to simplify your DAX significantly and sometimes to increase performance as well, you will want to avoid the snowflake schema in favor of the classic star schema. This is particularly true here, where the dimension tables that you’ve snowflaked off of the Supplier Pricelists table are are all single column tables, so they are not gaining you anything. They should all be deleted.

  2. I believe the Newly Bought Items table can be deleted from the model and the logic within can be recreated inside a measure if needed

  3. the Supplier Pricelists is a classic slowly changing dimension table. You are going to need to figure out how you want to manage changing prices over time. There are a number of different accepted strategies for doing so. I think in this case my preference would be to have a start and end date associated with each price (with the end date being blank for the current price) then in the appropriate measures you could build in the logic for selecting the correct price given the relevant date value within the fact table.

Here are some other relevant forum threads that lay out alternative approaches and the pros and cons of each.

I hope this is helpful to you.

– Brian

3 Likes