M/DAX - Create Category for every Date

[akbjf]

Hi all,

I have a table like this:

Now, I want to have additional column "Category", consisted of three unique values "A", "B", and "C", that repeats itself for every unique date. The end result should be like this:

Now, how can I make such column? I don't know what to use, whether DAX or M.

Can you help me?

Thank you! 

[v-kelly-msft · Accepted solution]

Hi @akbjf ,

You can also create a category table as below:

Then create a new table using below dax expression:

Table 2 = CROSSJOIN('Table','Table (2)')

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ryan_mayu]

@akbjf 

You can also try DAX to create a table.

Table 2 = 
VAR a=SUMMARIZE('Table','Table'[Date],'Table'[X],'Table'[Y],'Table'[Z],"Categroy","A")
VAR b=SUMMARIZE('Table','Table'[Date],'Table'[X],'Table'[Y],'Table'[Z],"Categroy","B")
VAR c=SUMMARIZE('Table','Table'[Date],'Table'[X],'Table'[Y],'Table'[Z],"Categroy","C")
return UNION(a,b,c)

[MFelix]

Hi @akbjf ,

On the query editor add a custom columns with the following code:

{"A", "B", "C"}

This will add a list table with all your categories then just expand the column that is created.

Check full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDJR0lSxBhASLMDZRidSCyRjBZM1OQrDmQMDQASscCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, X = _t, Y = _t, Z = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"X", Int64.Type}, {"Y", Int64.Type}, {"Z", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {"A", "B", "C"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"