Rank based on Measure for two tables

[Anonymous]

Hi guys, 

I'm having some trouble solving the following issue: 

I have two fact tables from different datasets that contain similar information, just for different time stamps. The 2nd table will only contain a subset of Product Keys from the first table that had an update in Period 2, so if the Product Key doesn't appear in the 2nd table, the Cost will be the the same as in Period 1:

Table 1:

Department Group	Product Key	Date	Cost
1	1	Period 1	100
1	2	Period 1	200
1	3	Period 1	300
2	4	Period 1	400
2	5	Period 1	500
2	6	Period 1	600

Table 2:

Department Group	Product Key	Date	Cost
1	1	Period 2	250
1	3	Period 2	350
2	4	Period 2	700
2	6	Period 2	650

Tables are linked with product & date table using the keys. I created a measure for Cost that refers to both tables, see simplified example: 

Cost = IF(ISBLANK(MIN(Table1[Cost])),

        MIN(Table2[Cost]),

        MIN(Table1[Cost]))

Now I'm trying to calculate the cost rank of each Product Key within the respective Department Group & Date based on the Cost measure I created. It needs to refer to the measure, because there are some adjustments I do on the data depending on which table it comes from within the measure (Cost measure is simplified). Probably it should create a temporary table that combines Table 1 & Table 2 and fills blanks in Table 2 for missing Cost values in Period 2 (just as the Cost measure does) with the Rank column being what I'm looking for:

Group	Product Key	Date	Cost	Rank
1	1	Period 1	100	1
1	2	Period 1	200	2
1	3	Period 1	300	3
2	4	Period 1	400	1
2	5	Period 1	500	2
2	6	Period 1	600	3
1	1	Period 2	250	2
1	2	Period 2	200	1
1	3	Period 2	350	3
2	4	Period 2	700	3
2	5	Period 2	500	1
2	6	Period 2	650	2

I hope I explained the problem well enough and someone can help me with this. 

Thanks in advance!

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Please create a new table:

Table = 
UNION (
    SELECTCOLUMNS (
        'Table1',
        "Department Group", 'Table1'[Department Group],
        "Product Key", 'Table1'[Product Key],
        "Date", 'Table1'[Date],
        "Cost", 'Table1'[Cost]
    ),
    SELECTCOLUMNS (
        'Table1',
        "Department Group",
            IF (
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
                    = BLANK (),
                'Table1'[Department Group],
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
            ),
        "Product Key", 'Table1'[Product Key],
        "Date2", "Period2",
        "Cost",
            IF (
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
                    = BLANK (),
                'Table1'[Cost],
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
            )
    )
)

You will get a table like this:

Create a rank column:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Department Group] = EARLIER ( 'Table'[Department Group] )
            && 'Table'[Date] = EARLIER ( 'Table'[Date] )
    ),
    'Table'[Cost],
    ,
    ASC
)

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Please create a new table:

Table = 
UNION (
    SELECTCOLUMNS (
        'Table1',
        "Department Group", 'Table1'[Department Group],
        "Product Key", 'Table1'[Product Key],
        "Date", 'Table1'[Date],
        "Cost", 'Table1'[Cost]
    ),
    SELECTCOLUMNS (
        'Table1',
        "Department Group",
            IF (
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
                    = BLANK (),
                'Table1'[Department Group],
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
            ),
        "Product Key", 'Table1'[Product Key],
        "Date2", "Period2",
        "Cost",
            IF (
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
                    = BLANK (),
                'Table1'[Cost],
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
            )
    )
)

You will get a table like this:

Create a rank column:

Rank = 
RANKX (
    FILTER (
        'Table',
        'Table'[Department Group] = EARLIER ( 'Table'[Department Group] )
            && 'Table'[Date] = EARLIER ( 'Table'[Date] )
    ),
    'Table'[Cost],
    ,
    ASC
)

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yadong Fang, 

Thanks for the input, this is great help!

I forgot to mention one additional case: It might happen that Table2 contains Product Keys that have not been existent in Period 1 and thus don't show in Table1.
I see in the formula for the new table that for everything that comes from Table2 you refer to Product Keys from Table1 and then add missing info which would lead to leaving out added Product Keys in Period2. How would I have to change the formula to cover also this additional case?

Many thanks in advance!

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

I add a new product key in period 2 which does not exist in period 1:

Create a new table:

New_Table = 
var _tab = UNION (
    SELECTCOLUMNS (
        'Table2',
        "Department Group", 'Table2'[Department Group],
        "Product Key", 'Table2'[Product Key],
        "Date", 'Table2'[Date],
        "Cost", 'Table2'[Cost]
    ),
    SELECTCOLUMNS (
        'Table1',
        "Department Group",
            IF (
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
                    = BLANK (),
                'Table1'[Department Group],
                LOOKUPVALUE (
                    'Table2'[Department Group],
                    'Table2'[Product Key], 'Table1'[Product Key]
                )
            ),
        "Product Key", 'Table1'[Product Key],
        "Date2", "Period 2",
        "Cost",
            IF (
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
                    = BLANK (),
                'Table1'[Cost],
                LOOKUPVALUE ( 'Table2'[Cost], 'Table2'[Product Key], 'Table1'[Product Key] )
            )
    )
)
return
DISTINCT(_tab)

Create the other new table:

New_Table2 = 
UNION(
SELECTCOLUMNS (
        'Table1',
        "Department Group", 'Table1'[Department Group],
        "Product Key", 'Table1'[Product Key],
        "Date", 'Table1'[Date],
        "Cost", 'Table1'[Cost]
    ), 
    SELECTCOLUMNS (
        'New_Table',
        "Department Group", 'New_Table'[Department Group],
        "Product Key", 'New_Table'[Product Key],
        "Date", 'New_Table'[Date],
        "Cost", 'New_Table'[Cost]
    ))

Create a rank column:

Rank = 
RANKX (
    FILTER (
        'New_Table2',
        'New_Table2'[Department Group] = EARLIER ( 'New_Table2'[Department Group] )
            && 'New_Table2'[Date] = EARLIER ( 'New_Table2'[Date] )
    ),
    'New_Table2'[Cost],
    ,
    ASC
)

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Your dax statement will not create a temp table, you need to use summarize or add column functions to create temp table and use that.

[Anonymous]

I don't really know what you're referring to. Of course it's not creating a temp table, that's what I'm asking help for. Using the measure in the report on the other will do the same from a logic perspective, it fills the blanks for missing values in P2.