how to create a calculated table having distinct values from one column in DAX inside Power BI Desktop

1
votes

I want create a new table having distinct values from the column [Delivery Date Key] from the table Fact_Sale.

[Delivery Date Key] columns have below mentioned values

2013-01-12

2013-01-15

2013-04-19

2013-04-18

Now I want to create a new table having the below values, considering only year and month. Date will be hardcoded as 1. Because want to group sales by Month and Year and not date.. So the final rows of the new table will be

2013-01-01

2013-04-01

I am trying to use the below DAX query but it seems that DISTINCT does not accept year function:

Date1 = DISTINCT(date(year('Fact Sale'[Delivery Date Key]),month('Fact Sale'[Delivery Date Key]),1))

I have added the image of error also.. Is there another way to deal this situation. I am stuck since 3 days on it..

enter image description here

2
powerbidax

2 Answers

2
votes

Here's one approach that might help - I have added a few more dates just to be sure that the output is correct.

enter image description here

The DAX formula that I am using:

Date1 = 
    var t = DISTINCT('Fact_Sale'[Delivery Date Key])
    var Result = SELECTCOLUMNS(t, "Adjusted Date", DATE(YEAR(Fact_Sale[Delivery Date Key]), MONTH(Fact_Sale[Delivery Date Key]), 1))
return
    DISTINCT(Result)

...and the final result:

enter image description here

0
votes

In my case the requirement was to create a distinct list of IP addresses from one of the columns in a Table.

I was trying to create a table in Transform Data screen, in Power Query.

Here's the code that worked for me.

= let
    Source = #"TABLENAME",
    firstCol  = #"TABLENAME"[Client Ip],
    IP = List.Distinct(firstCol),
    #"DistinctIP" = Table.FromColumns({IP}, {"IP"})
in
    #"DistinctIP"