Limits in Composite Model

[Anonymous]

Hi,

I have a Direct query on a SQL database (StationID  |  SerialNo  |  StartHour) and an excel import for a shift schedule. Now I want to calculate a new column with the type of shift (morning, late, night). The two tables has a relationship on StationID.

But this is not possible. Are there any limits in a composite Model?

Best regards

Timo

[v-diye-msft]

Hi @Anonymous 

Based on my test, I can add the calculated column under composite mode successfully, could you please share us the detailed error info?

Here are 2 solved cases that failed to add calculated column in composite mode, probably helps:

Disabling aggregation: https://community.powerbi.com/t5/Desktop/Composite-model-calculate-column-group/td-p/561013

Upgrade to the latest version : https://community.powerbi.com/t5/Issues/Calculated-Column-using-Composite-Model-broken-in-September/idi-p/515383#comments

It’s preferred to share us more details if the issue still exist.  Thanks!

Best regards,

Dina Ye

[Anonymous]

Hi @v-diye-msft ,

I am able to add a calculated column as well. 

My data structure is:

Table 1 (excel file)

LineID	Plant	MorningStart	LateStart	NightStart
TST	Test	6	14	22
TST2	Test2	7	15	23

Table 2 (direct query)

LineID	Serial No	Start Hour
TST	abc	10
TST	def	15
TST2	xyz	14

Now I want to add a calculated column "shift" based on the LineID

If 'Table 2'[Start Hour] smaller than 'Table 1'[Late Start] then "Morning" and filter by LineID. 

Any idea?

Best regards

Timo

[Anonymous]

Hi @v-diye-msft ,

I found a solution but it is not very nice.

I add three new columns with RELATED(MorningStart), RELATED(LateStart) and RELATED(NightStart). After that I add a fourth column with an IF - function 

IF(Start Hour < LateStart ; then ....)

But this is not possible with a direct query. Got an error by the RELATED columns.

"The column 'Tabelle1[MorningStart]' either doesn't exist or doesn't have a relationship to any table available in the current context."

BR 
Timo