cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akbjf
Helper I
Helper I

M/DAX - Create Category for every Date

Hi all,

 

I have a table like this:

date1.PNG

 

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:

 

date2.PNG

 

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

Can you help me?

 

Thank you! 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @akbjf ,

 

You can also create a category table as below:

Screenshot 2020-10-28 104622.png

Then create a new table using below dax expression:

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

And you will see:

Screenshot 2020-10-28 104741.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi @akbjf ,

 

You can also create a category table as below:

Screenshot 2020-10-28 104622.png

Then create a new table using below dax expression:

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

And you will see:

Screenshot 2020-10-28 104741.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

ryan_mayu
Super User
Super User

@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)

1.PNG





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

Proud to be a Super User!




MFelix
Super User
Super User

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"

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!