Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |