Forum Discussion

BusinessAnalyst's avatar
BusinessAnalyst
Icon for Helper I rankHelper 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), reven...
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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft 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