topic Re: GENERATALL + Treatas in DAX Commands and Tips

GENERATALL + Treatas

Amy_Qc — Tue, 25 Jul 2023 21:31:06 GMT

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

Re: GENERATALL + Treatas

OwenAuger — Wed, 26 Jul 2023 10:46:00 GMT

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

Re: GENERATALL + Treatas

Amy_Qc — Wed, 26 Jul 2023 13:40:00 GMT

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

Re: GENERATALL + Treatas

Amy_Qc — Thu, 27 Jul 2023 19:27:52 GMT

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