GENERATALL + Treatas

[Amy_Qc]

Hi,

my code does not show me all the rows of the two tables ( table1 and table2), I am missing something, please help me

Code:

Table_generat =

GENERATEALL(

    'Table1',

    CALCULATETABLE (

        SELECTCOLUMNS (

           'Table 2',

            "Var2-1",  'Table 2'[TOT_REG_SUPP],

            "Var2-2", 'Table 2'[SALR_TOT]

        ),

        TREATAS ( ROW ( "Num-EMP.", 'Table 1'[NUM_EMP1]), 'Table 2'[NUM_EMP] ),    

        TREATAS ( ROW ( "Num-CAP", 'Table 1'[NUM_CAP1]), 'Table 2'[NUM_CAP] ),

        TREATAS ( ROW ( "Date", 'Table 1'[Date_1]), 'Table 2'[Date] )   ,

 TREATAS ( ROW ( "CR",'Table 1'[CR_1]), 'Table 2'[CR] )        

    ))

thanks in advance

Amy

[OwenAuger]

Hi @Amy_Qc 

GENERATEALL returns a table joining all rows of the 1st argument with the table specified in the 2nd argument (evaluated for each row). The columns corresponding to the 2nd table argument will contain BLANK values if the 2nd argument is empty.

In your example

• Table_generat should contain all rows of 'Table1' (possibly with repetitions).
• Due to the filters applied in the 2nd argument, the columns of Table_generat resulting from the 2nd argument may not correspond to every row of 'Table2'.

Could you give more detail on what you're seeing with some examples, or a sample PBIX if possible (sanitised if needed)?

Regards

[Amy_Qc]

Hi @Auger ,

Thanks for your reply,

this is my exemple, with the same code. let me know if i use the correct code plz:

thanks in advance

Amy

[Amy_Qc · Accepted solution]

Hi,

I solved finaly my problem myself

In the Table 1, i add a new colomn with this code with another right key (Code sys) that there is more details level 4 with:

TOT_REG_SUP = LOOKUPVALUE('Table 2'[TOT_REG_SUPP],

 'Table 2'[Date],'Table 1'[Date1],

'Table 2'[NUM_EMP],'Table 1'[NUM_EMP1] ,

'Table 2'[NUM_CAP],'Table 1'[NUM_CAP1],

'Table 2'[Code sys],'Table 1'[Code sys1],0 )

 

and the same code for another variable, hope this help authers.

Regards

Amy