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 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?

Need Help

6 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    I'm not sure but try this

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

     

    • hrafnkel11's avatar
      hrafnkel11
      Helper I
      2 years ago

      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?

  • 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" }
    )
)