Forum Discussion

hrafnkel11's avatar
hrafnkel11
Helper I
2 years ago

Alternatives to ADDCOLUMNS for DirectQuery

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 t...
Need Help
AlexisOlson's avatar
AlexisOlson
Super User
2 years ago

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's avatar
hrafnkel11
Helper I
2 years ago

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.