Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kavitha10
Frequent Visitor

Dynamic column creation

Hi All,

 

Is there any way to dynamically create calculated columns for each of the values stored in the list/table.

I have a list/table with values A,B,C, I need to create columns - A with filter on A, B with filter on B and so on dynamically.

 

Any help is really appreciated.

 

Thanks in advance!

 

Edited:

Please refer to the example below for further clarification on my query.

For example, let’s consider

Kavitha10_0-1618310992613.png

Kavitha10_1-1618311040112.png

Kavitha10_2-1618311057368.png

If Date in Table-1 falls between Start Date and End Date of Table-2, then take sum of Amount from Table-1. So, I need to dynamically create columns for each of the distinct values of Company Name from Table-1 (as shown in Result of Table-2 to be) using M query.

 

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

Hi  @Kavitha10 ,

 

You could do it using dax expression:

Measure = 
CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),'Table1'[Date]>=MAX('Table2'[Start Date])&&'Table1'[Date]<=MAX('Table2'[End Date])&&'Table1'[Company Name]=MAX('Table1'[Company Name])))

And you will see:

v-kelly-msft_0-1618365116579.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

10 REPLIES 10
v-kelly-msft
Community Support
Community Support

Hi  @Kavitha10 ,

 

You could do it using dax expression:

Measure = 
CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),'Table1'[Date]>=MAX('Table2'[Start Date])&&'Table1'[Date]<=MAX('Table2'[End Date])&&'Table1'[Company Name]=MAX('Table1'[Company Name])))

And you will see:

v-kelly-msft_0-1618365116579.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Thank you so much! very helpful! 🙂

Anonymous
Not applicable

A clarification: but the dates in [start] and [end] are only by chance the beginning and the end of the month or is it always like this?

 

Could you upload in somewhere a file (excel?) containing the examples table?

JW_van_Holst
Resolver IV
Resolver IV

In additon to my earlier response. Completely solving the problem in M is more easy than constructing the extended table with empty columns.

Capture2.PNG

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUNdQ1MjAyBDENDJRidaDCFrpGMHEjJHEjY4S4MVTcCaTeFGGOCZK4ua4xTNgUKuwMMsYIYYwZSDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Date = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Date", type date}, {"Amount", Int64.Type}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Month", {"Company Name", "Start of Month", "End of Month"}, {{"Total Amount", each List.Sum([Amount]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Company Name"]), "Company Name", "Total Amount", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A", "B", "C"})
in
    #"Replaced Value"
JW_van_Holst
Resolver IV
Resolver IV

I assume you need this result:

Capture1.PNG

 

You need 3 simple queries:

= List.Distinct(Transactions[#"Company Name"])
= #table(Query1,{})
= StartEnd&Query2

Good Luck,

//JW

ERD
Super User
Super User

Hi @Kavitha10 ,

I don't know all your prerequisites but just in case: are you sure you need to add anything in M?

As far as I can see from your example, your second table consists of start date and end date of each month. Thus the result can be achieved by:

1. Creating a proper Calendar table with columns for start date and end date.

2. Creating relation between Fact table (Date column) and Calendar table (Date column).

3. Build matrix:

ERD_1-1618325023404.png

 

ERD_0-1618325003812.png

 

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

v-kelly-msft
Community Support
Community Support

Hi  @Kavitha10 ,

 

Do you wanna get something as below:

v-kelly-msft_0-1618307738658.png

If so,create a measure as below:

Measure = CONCATENATEX(ALLSELECTED('Table'[Value]),[Value],",")

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Hi, Thank you for the response. Please refer to the above edited query. I have elaborated my requirement.

Vera_33
Resident Rockstar
Resident Rockstar

Hi @Kavitha10 

 

Can you provide some sample data with Expected results?

Hi, Please refer to the above edited query.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors