New composite model - DAX help - calculation cost * countrows()

[benfedit]

hi all,

I'm using the new feature "direct query for power bi and azure analysis services".

My data sources are as follows:

Fact table (800M rows) (azure analyisis services)

 -Excel_Key (Calculated column)

Excel Table (20k rows) (Excel imported)

 - Key (calculated column)

 - cost

There is a on to many relation ship from Excel table to the fact table.

The columns used to link the two tables are both calculated columns (combinevalues(";"col1,col2,col3)

What I would like to ahcieve is sometijg like this:

total cost= Cost * countrows(Fact)

What ever I tried so far gives me either wrong total or an "unexpected error".

1) I tired to bring the cost column to my fact table using related but does not seem to work with the composite model.

2) I tried the sumx approach (which works perfectly fine on an usual tabular model) but I get the following error : "An unexpected error occurred (file 'xldqas.cpp', line 510, function 'DAXDefManager-AddVariableOrlnlineExpr')"

Any thoughts?

thanks

[Anonymous]

I am having the same issue and error. Composite model (Power BI Dataset Direct Query + Other Tables Import)

If I open up DAX STUDIO the measure runs fine but for whatever reason, the Visual in Power BI Desktop cannot display.

[benfedit]

Hi Jake,

So I'm not alone in having this issue...

The only workaround that seems to work so far at least for me and Nexter is to make sure that you do not bring the column from the import table into your visual and that should do the trick.

I'm sure that it willget fixed soon but for now we have to take this into consideration while designing our models and using the new composite feature.

[amitchandak]

@benfedit , One is a column and one is the measure. What are trying a new column or new measure

Measure using a common dim

total cost= Sumx(Values(Dim[dim]) , Min([Cost]) * countrows(Fact))

a new column, but that will work when both tables are import mode

total cost= Cost * countrows(Fact)

[benfedit · Accepted solution]

Hi  Amitchandak,

Thanks for the suggestion.

So I seem to have found the "solution" which is more like a work around but doesn't really matter as the whole project is already a workaround 🙂

So it seems that with the new composite model sumx works fine only and only when you don't put columns from the dim into the visual.

As soon as you add any columns of the dim into the visual with the sumx measure it throws an error "An unexpected error occurred (file 'xldqas.cpp', line 510, function 'DAXDefManager-..."

I gess Microsoft will fix it at some point we're only about a month since the release of this new massive feature so can't complain at all 🙂

[Nexter]

I'm having this same issue right now, thanks for bringing it up!!  I'm trying to use averagex, it works when shown as an aggregated number, but the second I try to bring in the columns from dimension table in the composite model it gives me that same error.  

In the meantime, I've been able to get around it be avoiding the averagex and simply dividing the measure by a count of the dimension table rows, although this gives slightly different numbers in the aggregate, but I'll look into that.