Is there a better way for this DAX Calculated Column

[arzukari]

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.

[lbendlin · Accepted 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

[mahoneypat · Accepted solution]

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

[lbendlin · Accepted 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