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

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

    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