Forum Discussion

kunal_mehta1's avatar
kunal_mehta1
Icon for Helper I rankHelper I
3 years ago

EXCEL FORMULA

Hi All, I have attached a sample data where i have two sheets in Excel. 
The data which i want from sheet 2 in sheet 1 is cloud classifciation column in sheet 1 cloud classification column but based on the condition that it picks the cloud classification value based on the latest date for the same name which exactly matches in sheet 1 and sheet 2.

"Need Help"

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I assume the two tables have the relationship like below.

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    Classification CC =
SUMMARIZE (
    CALCULATETABLE (
        Sheet2,
        TREATAS (
            GROUPBY (
                RELATEDTABLE ( Sheet2 ),
                Sheet2[Name],
                "@latest", MAXX ( CURRENTGROUP (), Sheet2[Date] )
            ),
            Sheet2[Name],
            Sheet2[Date]
        )
    ),
    Sheet2[Classification]
)

     

  • kunal_mehta1's avatar
    kunal_mehta1
    Icon for Helper I rankHelper I
    3 years ago

    I am sorry Jihwan_Kim . I am actually looking for an Excel formula. If you can help me with it? I accidentally posted here on the Power BI community.