topic Re: Creating Table B from Table A with Specific Conditions and Aggregation in DAX Commands and Tips

Creating Table B from Table A with Specific Conditions and Aggregation

minseon — Thu, 07 Dec 2023 02:03:48 GMT

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

Re: Creating Table B from Table A with Specific Conditions and Aggregation

ChiragGarg2512 — Thu, 07 Dec 2023 09:33:19 GMT

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])) )

Re: Creating Table B from Table A with Specific Conditions and Aggregation

minseon — Thu, 07 Dec 2023 11:01:55 GMT

thank you 🙂