Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Limits in Composite Model

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

Need Help

3 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-diye-msft ,

       

      I am able to add a calculated column as well. 

      My data structure is:

       

      Table 1 (excel file)

      LineIDPlantMorningStartLateStartNightStart
      TSTTest61422
      TST2Test271523

       

      Table 2 (direct query)

      LineIDSerial NoStart Hour
      TSTabc10
      TSTdef15
      TST2xyz14

       

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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