Addcolumns with text

[charleshale]

What do we think is the best way with extremely large tables to append one column to another with dax assuming both  are values?   I am using crossjoin and it's killing me with processing load.

So I am wondering if I can summarize one column and then use either lookupvalue or  addcolumns.  The construction below fails

Var _1 = 
Summarize (Table1, Table1[Column])

Var _2
Addcolumns (_1 , "Name", values(Table2[Column2])

Return
...

 and yields a message of muliple values supplied whereas single expected, even if I try it as adding a table of:
RETURN 

_2

[parry2k · Accepted solution]

@charleshale here how dax will change:

Filter Intersect = 
VAR __countEmails = 
CALCULATE ( 
    COUNTROWS ( EmailSubs ), 
    USERELATIONSHIP ( EmailSubs[Email], Lookup[Email] )   //in case this EmailSubs table has inactive relatiobship
)  
VAR __countUsers = 
    CALCULATE ( 
        COUNTROWS ( 'Registered USers' ), 
        USERELATIONSHIP ( 'Registered USers'[Registered User], Lookup[RegsteredUser#] )    //in case Registered USers table has inactive relatiobship
    )
RETURN
IF ( __countEmails  == BLANK() ||  __countUsers == BLANK() || MAX ( Lookup[Email] ) == BLANK(), BLANK() , 1 )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@charleshale whatever is your inactive relationship, use USERELATIONSHIPin the measure for those tables. It should work. 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@charleshale solution attached.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[charleshale]

@parry2k I so want to accept your solution for the power of the logic but I can't becuase I am dealing with inactive relationships here, which is root cause of not being able to use intersect.  In the example, if I make the relationship inactive, the code fails.   I feel like I need an intersect with treatas or related or userrelationship

[charleshale]

Whoa what dark magic is this?   I have never seen anything like this in PowerBi even through years of toil.   Examining now....

________________________

Filter Intersect =

VAR __countEmails = COUNTROWS ( EmailSubs )

VAR __countUsers = COUNTROWS ( 'Registered USers' )

RETURN

IF ( __countEmails == BLANK() || __countUsers == BLANK() || MAX ( Lookup[Email] ) == BLANK(), BLANK() , 1 )

[parry2k]

@charleshale hand for few minutes, sending the pbix file with two solutions and you can pick whatever works best for you.

[parry2k]

@charleshale I simply did a measure without doing anything crazy and found this is the only registered user that has an email and that's true based on the data:

[charleshale]

Hi @parry2k .  What measure did you use?   When I try to intersect emailsub and registereduser, I get 

Feedback Type:
Frown (Error)

Timestamp:
2021-01-28T00:52:56.6150922Z

Local Time:
2021-01-27T16:52:56.6150922-08:00

Session ID:
cb9b517d-1b34-4f72-a366-b0e41d6fcc8a

Release:
December 2020

Product Version:
2.88.1385.0 (20.12) (x64)

Error Message:
MdxScript(Model) (4, 31) Calculation error in measure 'EmailSubs'[intersect]: Function 'INTERSECT' does not support joining a column of type Text with a column of type Number.

OS Version:
Microsoft Windows NT 10.0.18363.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528040]

Peak Virtual Memory:
38.6 GB

Private Memory:
540 MB

Peak Working Set:
1.14 GB

IE Version:
11.1198.18362.0

User ID:
12068d6d-f526-49fa-8c9a-c569eae64ff1

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\charl\Microsoft\Power BI Desktop Store App\FrownSnapShota3a3a836-58d0-4b73-b541-f3c9193fb8e2.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Is Report V3 Models Enabled:
True

Performance Trace Logs:
C:\Users\charl\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_NewWebTableInference
PBI_v3ModelsPreview

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_JsonTableInference
PBI_ImportTextByExample
PBI_ExcelTableInference
PBI_qnaLiveConnect
PBI_eimInformationProtectionForDesktop
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_narrativeTextBox
PBI_dynamicParameters
PBI_anomalyDetection
PBI_newFieldList
PBI_cartesianMultiplesAuthoring

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
200%

Supported Services:
Power BI

Formulas:

section Section1;

shared EmailSubs = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysrMdahMzMjP10vOz1WK1QGKJOamFqOJJeUnoYtk5uRUOqTn56fnpMIFE/NSilIT0UUNHQzhbCMHIwg7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}})
in
#"Changed Type";

shared RegisteredUsers = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApCWYNDSAUBDdhkYolDFUDmIQiIoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RegUser#" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RegUser#", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"RegUser#"})
in
#"Removed Columns";

shared Lookup = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysrMdahMzMjP10vOz1XSUTJUitUBiibmphajipuAJZLyk1CEjcCihg6GUD5ElbGDMZRvCuabOJhA+WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, #"RegsteredUser#" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"RegsteredUser#", Int64.Type}})
in
#"Changed Type";

shared #"Registered USers" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Registered User" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Registered User", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",6,25,Replacer.ReplaceValue,{"Registered User"})
in
#"Replaced Value";

[charleshale]

what measure did you use?

[parry2k]

@charleshale what happens if a  registered user has multiple emails?

[charleshale]

For starters, I'd take the first.  I was trying FirstNonBlankValue but having trouble using that with addcolumns and values

[parry2k]

@charleshale looking at your pbix file, what is your goal? What you want the expected result? For a second forget about the solution to how to get there.

[charleshale]

On the PBIX file the only intersect between email and registered user IDs would be the first row if these were the 3 tables (jim@yahoo.com).   Basically, another way to ask the question is "what's the best way to do a 3 way intersect!"?    (PS assuming there are inactive relationships involved)

[parry2k]

@charleshale what is your ultimate goal, do you want to visualize the user which are common between two tables or what?

[charleshale]

The ultimate goal is an Intersect between 2 table columns that have an inactive relationship.

Specifically, I want to lok at the overlap  between emailsubs and registereduserIDs but maintain an indirect relationship between the two.   So I could simply take my registereduserIDs table, do a lookupvalue and add email address, and then intersect....and that would work but I am trying to see if I can avoid adding the tables, which is probably dumb since they are 10m row tables and it's much more efficient to add columns

[charleshale]

And by the way, what's the best practice to summarize a column of values and then add a column of related values without adding a whole table to each rown?    I'd normally use crossjoin but am getting killed on memory usage

[parry2k]

@charleshale it should be straightforward, I wouldn't create a table or something crazy but just work with the measure, and you can use INTERSECT dax function depending what you want

[charleshale]

Intersect works on a direct relationship.  How would you use intersect on an indirect relationship?     Ie intersect with a TREATAS() or USERRELATIONSHIP()?

[mahoneypat]

Your expression is adding the whole table to each row, which is why you are seeing that error.  Can you show some example data of what you two input tables look like and your desired output?

Pat

[parry2k]

@charleshale values function returns a table of unique values, in this on values of column2 and that's why you are getting this error. What you are trying to achieve?