CODEWITHSUNDEEP
daxpowerbi-desktop
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

Remove Duplicate Rows from Date Table

I created a Date Table that exists in my Data view.

I created it based on the dates in another table.

This Date table has duplicate values in it.

How can I remove the duplicate values?

The way I created it basically looks like this:

d_Date = Calendar(min('Table_1'[sales_date]),max('Table_1'[Sales_Date]))

How can I remove duplicate values? Any suggestions?

I tried doing this, but it didn't work:

d_Date = DISTINCT(Calendar(min('Table_1'[sales_date]),max('Table_1'[Sales_Date])))

Upvotes: 0

Views: 381

Answers (1)

AntrikshSharma
AntrikshSharma

Reputation: 661

If the end goal is to create a date table then simply use this code and then you can create more columns inside ROW functions.

Dates =
VAR ListOfDate =
    VAR MinDate =
        MIN ( Sales[Order Date] ) -- Change with the column reference of your table
    VAR MaxDate =
        MAX ( Sales[Order Date] ) -- Change with the column reference of your table
    VAR StartDate =
        DATE ( YEAR ( MinDate ), 1, 1 ) -- DATE ( 2021, 1, 1 )
    VAR EndDate =
        DATE ( YEAR ( MaxDate ), 12, 31 ) -- DATE ( 2021, 12, 31 )
    VAR Result =
        CALENDAR ( StartDate, EndDate )
    RETURN
        Result
VAR Result =
    GENERATE (
        ListOfDate,
        VAR CurrentDate = [Date]
        RETURN
            ROW (
                "Month", FORMAT ( CurrentDate, "MMMM" ),
                "Month Number", MONTH ( CurrentDate ),
                "Calendar Year", "CY " & YEAR ( CurrentDate ),
                "Calendar Year Number", YEAR ( CurrentDate )
            )
    )
RETURN
    Result

Upvotes: 1

Related Questions