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 co...
  • 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]))
)
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]))
)
  • minseon's avatar
    minseon
    Frequent Visitor
    2 years ago

    thank you 🙂