March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Folks,
Wondering how we create a measure based on a dataset <Not only specific to date column> while union it back to the original dataset ?
May be it make it easier to view the problem visually, so i have created the picture below.
Link: Link
P.S. Somehow i have to do it in this way
thanks
Solved! Go to Solution.
And you can also use a DAX Calculated Table as well.
Assuming your Table Name is Table1.
From Modelling Tab >> New Table
File attached as well
Calculated Table = VAR Rows_I_need = GENERATE ( SELECTCOLUMNS ( VALUES ( Table1[Category] ), "Mycategory", [Category] ), CALCULATETABLE ( TOPN ( 1, Table1, [Date], DESC ) ) ) VAR Add_Volumn_Difference = ADDCOLUMNS ( Rows_I_need, "Difference", [Volume] - CALCULATE ( SUM ( Table1[Volume] ), TOPN ( 1, FILTER ( Table1, Table1[Category] = [Mycategory] && Table1[Date] < EARLIER ( [Date] ) ), [Date], DESC ) ) ) VAR ComparisonTable = SELECTCOLUMNS ( Add_Volumn_Difference, "Date", [Date], "Category", [Category], "Calc Type", "Comparison", "Volumn", [Difference] ) RETURN UNION ( Table1, ComparisonTable )
Hi Folks,
Wondering how we create a measure based on a dataset while union it back to the original dataset ?
May be it make it easier to view the problem visually, so i have created the picture below.
Link: Link
thanks
HI @Peter_Price
Try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zW0UNJRcgRiz7yUzLLMlNLEHCDHUClWB1WJE7oSIwwlzuhKjCFKjPBYZIKhBMMiUwwlGBaZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, #"Calc Type" = _t, Volume = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Calc Type", type text}, {"Volume", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Date", each List.Max([Date]), type date}, {"All Rows", each _, type table}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Max Rows", each let mydate=[Date] in Table.SelectRows([All Rows],each [Date]= mydate)), #"Expanded Max Rows" = Table.ExpandTableColumn(#"Added Custom1", "Max Rows", {"Volume"}, {"Max Rows.Volume"}), #"Added Custom" = Table.AddColumn(#"Expanded Max Rows", "Day Defore", each let mydate=[Date] in Table.SelectRows([All Rows],each [Date]= Date.AddDays(mydate,-1))), #"Expanded Day Defore" = Table.ExpandTableColumn(#"Added Custom", "Day Defore", {"Volume"}, {"Day Defore.Volume"}), #"Added Custom2" = Table.AddColumn(#"Expanded Day Defore", "Volume", each [Max Rows.Volume]-[Day Defore.Volume]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"All Rows", "Max Rows.Volume", "Day Defore.Volume"}), #"Added Custom3" = Table.AddColumn(#"Removed Columns1", "Calc Type", each "Comparison"), #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom3"}) in #"Appended Query"
File attached as well
You can follows the steps from Query Editor
And you can also use a DAX Calculated Table as well.
Assuming your Table Name is Table1.
From Modelling Tab >> New Table
File attached as well
Calculated Table = VAR Rows_I_need = GENERATE ( SELECTCOLUMNS ( VALUES ( Table1[Category] ), "Mycategory", [Category] ), CALCULATETABLE ( TOPN ( 1, Table1, [Date], DESC ) ) ) VAR Add_Volumn_Difference = ADDCOLUMNS ( Rows_I_need, "Difference", [Volume] - CALCULATE ( SUM ( Table1[Volume] ), TOPN ( 1, FILTER ( Table1, Table1[Category] = [Mycategory] && Table1[Date] < EARLIER ( [Date] ) ), [Date], DESC ) ) ) VAR ComparisonTable = SELECTCOLUMNS ( Add_Volumn_Difference, "Date", [Date], "Category", [Category], "Calc Type", "Comparison", "Volumn", [Difference] ) RETURN UNION ( Table1, ComparisonTable )
Hi,
If the entries in the Date column are continuous, then try this
Hope this helps.
Hi Ashish,
thanks for your reply and it is great.Sry that i didn't make myself clear. Somehow i want to do
Create a measure based on existing dataset , it may involve sum(Cat B) - Sum(Cat C) < Not only specific to date column>
then union that measure back to origninal dataset
Thx
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
157 | |
97 | |
79 | |
69 |