dynamically compare two tables, label the differences.

[BusinessAnalyst]

Dear experts,

I would like to consult you in comparing two tables with the conditions as below:

Table 1 contains all revenue data: ID, revenue (by week/year), time of sales (by week/year), revenue type

Table 2 contains all IDs and time of production (by week/year)

ID column in table 1 is a sub dataset of ID column in table 2

Here is the Sample data

I would like to create a visual with 2 columns: Column 1 contains all IDs (taken from table 2), Column 2: type

For example: I choose week/year sale from slicer = 04/2016 (=201604)

- ID 19: YearWeek.Production = 201639 > YearWeek.Sales = 201604. It means that this ID was made after the chosen sale time -> label: "didn't make yet"

- ID 39:
YearWeek.Production = 201532 < YearWeek.Sales = 201604
but it doesn't appear in table 1
-> label: "didn't sell yet"

- ID 100:

YearWeek.Production = 201550 < YearWeek.Sales = 201604
it appears in table 1

-> assign the relevant type (in this example it is: "medium") ->Label "medium"

ID		Type
100		Medium
19		didn't make yet
39		didn't sell yet
17		low
25		low
26		….
28		….

Thank you very much for your consideration and help!

With kind regards,

Cindy

[v-ljerr-msft · Accepted solution]

Hi @BusinessAnalyst,

According to your description above, you should be able to use the formula below to create the measure for type, and show it with IDs from table2 on the report.

TypeForSelection = 
IF (
    HASONEVALUE ( Table2[ID] ),
    IF (
        MAX ( Table2[YearWeek.Production] ) > MAX ( Table1[YearWeek.Sales] ),
        "didn't make yet",
        IF (
            CALCULATE (
                COUNTROWS ( Table1 ) > 0,
                FILTER ( Table1, Table1[ID] = MAX ( Table2[ID] ) )
            ),
            CALCULATE (
                FIRSTNONBLANK ( Table1[Type], 1 ),
                FILTER ( Table1, Table1[ID] = MAX ( Table2[ID] ) )
            ),
            "didn't sell yet"
        )
    )
)

Here is the modified sample pbix file for your reference.

Regards