Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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 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]))
)
thank you 🙂