Forum Discussion

BusinessAnalyst's avatar
BusinessAnalyst
Helper I
9 years ago
Solved

dynamically compare two tables, label the differences.

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

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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.:smileyhappy:

     

    Regards

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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.:smileyhappy:

     

    Regards