Forum Discussion

charleshale's avatar
charleshale
Continued Contributor
5 years ago
Solved

Addcolumns with text

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.   S...
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
parry2k
Super User
5 years ago

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's avatar
    charleshale
    Continued Contributor
    5 years ago

    what measure did you use?

  • charleshale's avatar
    charleshale
    Continued Contributor
    5 years ago

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