Alternatives to ADDCOLUMNS for DirectQuery

[hrafnkel11]

Hi - I'm in the process of splitting apart one Power BI report into two. Semantic Model 1 will house historical Contact/Account data with associated measures and Report B will house test score data that needs to reference Semantic Model 2. We're doing this using a composite model with a live connection back to Semantic Model 1.

I am currently determining if a contact passed using a measure that is a large SWITCH statement that defines the separate qualification rules based on each contact profile.

Certified = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Contact'[Role]) = "Sales",1,
    SELECTEDVALUE('Contact'[Role]) = "Academic",1,0
	)

My issue is that I then use a SUMX ADDCOLUMNS measure to aggregate the above, which does not appear to be allowed in DirectQuery.

# Certified = 
SUMX(  
        ADDCOLUMNS(
            'Contact',
             "Certified",[Certified] 
             ), 
        [Certified]
		)

 Is there maybe a better way for me to approach this?

[AlexisOlson]

I would try this first:

# Certified = SUMX ( 'Contact', [Certified] )

[hrafnkel11]

Thanks! I actually tried this first, but got an error. It turned out the error was because the live connected model had an unrelated column error. Now when I do this, I don't get an error, but Power BI just spins. Seems to be unable to produce the calculation.

[AlexisOlson]

Iterating through a table and doing a context transition and a switch for each row is a really inefficient way to do this calculation.

I'd recommend something more like this:

# Certified =
CALCULATE (
    COUNTROWS ( Contact ),
    Contact[Role] IN { "Sales", "Academic" }
)

Or like this

# Certified =
COUNTROWS (
    FILTER (
        Contact,
        Contact[Role] IN { "Sales", "Academic" }
    )
)

[hrafnkel11]

Thanks Alexis. I agree there's likely a much better way, but I probably also overly simplified the Certified measure in my example. The end user has a different set of criteria for each of these Roles. It's more like this (which is still simplified):

Certified = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('Contact'[Role]) = "Sales" && [Sales Test Passes]>0,1,
    SELECTEDVALUE('Contact'[Role]) = "Academic" && [Academic Test Passes]>0,1,0
	)

Where each SWITCH validation is using an entirely separate set of rules for each Role. I'm returning a boolean, then iterating that result over the contact table. The desired result is that for every contact record, I know if they are certified or not. I also can't do a calculated column, as it requires filter context.

In your example, that would allow me to count the contact records themselves, but not only those where the Certified boolean = 1.

[Ahmedx]

I'm not sure but try this

# Certified = 
SUMX(  
        SUMMARIZE(
            'Contact','Contact'[Role],
             "Certified",[Certified] 
             ), 
        [Certified]
		)

[hrafnkel11]

Thanks! I tried this, as well as a couple other approaches. Power BI seems to just spin. Seems to be unable to produce the calculation. My guess is maybe it doesn't like the SUMX being used against the live connected data source?