Create a table of distinct value from another table

[nywi6100]

Hi there. I have a table which comes from a data flow. It is a table table at the day level. I have the table from the flow as FiscalDate. I would like to create a new table from this flow which will give me three columns back fromt his table. The Unique combinations from these three columns in the FiscalDate table - Year, Month Name, Date Sort. not sure of the right/best function to do this with. 

[amitchandak · Accepted solution]

@nywi6100 ,

You can do something like this.

Summarize(Table,Table[Year],Table[Month Name],Table[Date Sort])

I usually create a new table using the Calendar function. Use Year start Date and Year-end date function to create the financial year I want. Refer to this link: https://www.dropbox.com/s/wrcyk5j66corvjg/Apr2Mar-Cal.pbix?dl=0

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[nywi6100]

Thanks, worked like a charm!

[AiolosZhao]

Hi @nywi6100 ,

I think you can use the below DAX to create a table with distinct value from another table:

1. Click the "Modeling" tab

2. Choose the "New Table" button

3. Using the below DAX

Table 2 = DISTINCT(SELECTCOLUMNS(From_table,"column1",CONCATENATE(Year,Month,Date)))

Please try, any questions, please tell me.

Aiolos Zhao