Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Rank based on Measure for two tables

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 ...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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's avatar
    v-yadongf-msft
    3 years ago

    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.

v-yadongf-msft's avatar
v-yadongf-msft
Community Support
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      v-yadongf-msft
      Community Support
      3 years ago

      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.