Forum Discussion

minseon's avatar
minseon
Frequent Visitor
2 years ago
Solved

Creating Table B from Table A with Specific Conditions and Aggregation

Hi all
i have issue during makeing new table

-------

Table A has columns ITEM_CD, B_CD, DEPT_CD, C_NO, CREATED_AT, and MASK_YN. I want to create a new table B from this table with the following conditions.

1. table B has 4 columns. item_cd, B_CD, dept_cd, created_at
2.table A MASK_YN = 'Y'
3. the ITEM_CD column in B has the fastest CREATED_AT per ITEM_CD in A table
4. the ITEM_CD column in B must be a unique value
5. the B_CD, DEPT_CD, CREATED_AT column values in table B are the B_CD, DEPT_CD, CREATED_AT column values that map to the A table ITEM_CD column values based on the ITEM_CD extracted in step 3.

I tried below, but it didn't work
result tbale has duplicated item_cd

 

 

 

 

 

 

 

 

 

 

 

can you solve this issue??

thanks

 

  • ChiragGarg2512's avatar
    ChiragGarg2512
    2 years ago

    minseon Try the following DAX,

    TableB = 
SUMMARIZE(
    FILTER(
        'TableA',
        'TableA'[MASK_YN] = "Y"
    ),
    [ITEM_CD],
    "B_CD", MAX('TableA'[B_CD]),
    "DEPT_CD", MAX('TableA'[DEPT_CD]),
    "CREATED_AT", CALCULATE(MAX('TableA'[CREATED_AT]), ALLEXCEPT('TableA', 'TableA'[ITEM_CD]))
)

2 Replies

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Icon for Solution Sage rankSolution Sage
    2 years ago

    minseon Try the following DAX,

    TableB = 
SUMMARIZE(
    FILTER(
        'TableA',
        'TableA'[MASK_YN] = "Y"
    ),
    [ITEM_CD],
    "B_CD", MAX('TableA'[B_CD]),
    "DEPT_CD", MAX('TableA'[DEPT_CD]),
    "CREATED_AT", CALCULATE(MAX('TableA'[CREATED_AT]), ALLEXCEPT('TableA', 'TableA'[ITEM_CD]))
)