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

I would try this first:

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

 

  • hrafnkel11's avatar
    hrafnkel11
    Helper I
    2 years ago

    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'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.