Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
For the longest time, I've been writing my CALCULATE function like this:
CALCULATE(<expression>, FILTER(<table>,<filter>))
As opposed to just:
CALCULATE(<expression>, <filter>)
This is mainly due to this problem. I have a table that I would like to summarize the distinct userIDs from.
Table1:
userID | Sample |
A | 10 |
A | 15 |
B | 10 |
C | 20 |
D | 15 |
B | 20 |
Calculated Table2 [SUMMARIZE(Table1,userID)]:
userID |
A |
B |
C |
D |
Now I want to add a calculated column into Table2 taking the first Sample number from Table1. So I used:
CALCULATE(FIRSTNONBLANK(Table1[userID],1), FILTER(Table1, Table1[userID]=Table2[userID]))
That's because I couldn't refer to Table1[userID]=Table2[userID] in CALCULATE filter; meaning I couldn't just do this:
CALCULATE(FIRSTNONBLANK(Table1[userID],1), Table1[userID]=Table2[userID])
Needless to say, the performance is terrible, especially when dealing with millions of rows. Is there a better approach to this formula?
I should note that the example is oversimplified. The requirements usually deal with multiple filters and many logical expressions. I'd rather use CALCULATE in these calculated columns as opposed to adding them in ADDCOLUMNS, SUMMARIZECOLUMNS, or SUMMARIZE, mainly for readability.
Solved! Go to Solution.
So you don't want to use this version?
Table2 =
var a = values(Table1[userID ])
var b = ADDCOLUMNS(a,"first sample",FIRSTNONBLANK(Table1[Sample],[userID ]))
return b
If you only need the first row for each UserID, you could do this in query to avoid having two tables in your model and wasted space. This M code gets your desired result from your example data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrVgTJNwUwnhKgzkGkEYbqgKgCJxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"userID " = _t, Sample = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"userID ", type text}, {"Sample", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"userID "}, {{"AllRows", each _, type table [#"userID "=nullable text, Sample=nullable number]}}),
#"Added Prefix" = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.First(_)}}),
#"Expanded AllRows" = Table.ExpandRecordColumn(#"Added Prefix", "AllRows", {"Sample"}, {"Sample"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Sample", Int64.Type}})
in
#"Changed Type1"
If you do need the original table too, you could also calculate it with a measure instead of a calculated table (if you don't need the sample # as axis, legend, but only for analysis.
Also, writing your CALCULATE expressions with or without FILTER is appropriate and needed sometimes, so no problem there.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
If you only need the first row for each UserID, you could do this in query to avoid having two tables in your model and wasted space. This M code gets your desired result from your example data. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrVgTJNwUwnhKgzkGkEYbqgKgCJxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"userID " = _t, Sample = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"userID ", type text}, {"Sample", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"userID "}, {{"AllRows", each _, type table [#"userID "=nullable text, Sample=nullable number]}}),
#"Added Prefix" = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.First(_)}}),
#"Expanded AllRows" = Table.ExpandRecordColumn(#"Added Prefix", "AllRows", {"Sample"}, {"Sample"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Sample", Int64.Type}})
in
#"Changed Type1"
If you do need the original table too, you could also calculate it with a measure instead of a calculated table (if you don't need the sample # as axis, legend, but only for analysis.
Also, writing your CALCULATE expressions with or without FILTER is appropriate and needed sometimes, so no problem there.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
So you don't want to use this version?
Table2 =
var a = values(Table1[userID ])
var b = ADDCOLUMNS(a,"first sample",FIRSTNONBLANK(Table1[Sample],[userID ]))
return b
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |