Addcolumns with text

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

________________________

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

It's really probably dumb.   I have 3 huge tables.   They are

1. Email subs by email address

2. Registered users by #

3. A user lookup table matching 1 and 2.

I am trying to figure out the intersection between 1. and 2.      If I were practical, I would simply add a column to table 2 of email address using lookupvalue () and then user Intersect().

However, I am trying to see if I can do this virtually without killing the memory load by using crossjoin.

Maybe I should just be practical?

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

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

On the PBIX file the only intersect between email and registered user IDs would be the first row if these were the 3 tables ([email protected]).   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)

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

what measure did you use?

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";