Forum Discussion
Addcolumns with text
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.⚡
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:
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";