Merge multiple tables using GENERATEALL

[Anonymous]

First, let me say that many of the tables I intend to use are calculated tables and thus I cannot use M/PowerQuery to do my merging.

I am using the following the following DAX query to merge two tables:

PDPDAllDentalMerge =

    GENERATEALL(

            tbl_PDPD_INFO,

                CALCULATETABLE(

                    SELECTCOLUMNS(

                        'PSView AllDental_Part00',

                            "IN AnnualMax", 'PSView AllDental_Part00'[IN_AnnualMax], "IN ClassI", 'PSView AllDental_Part00'[IN_ClassI], "IN_ClassII", 'PSView AllDental_Part00'[IN_ClassII], "IN ClassIII", 'PSView AllDental_Part00'[IN_ClassIII], "IN ClassIV", 'PSView AllDental_Part00'[IN_ClassIV], "IN DeductibleIndiv", 'PSView AllDental_Part00'[IN_DeductibleIndiv], "IN Endo", 'PSView AllDental_Part00'[IN_Endo], "IN EndoClass", 'PSView AllDental_Part00'[IN_EndoClass], "IN EndoPulpotomy", 'PSView AllDental_Part00'[IN_EndoPulpotomy], "IN EndoPulpotomyClass", 'PSView AllDental_Part00'[IN_EndoPulpotomyClass], "IN Implants", 'PSView AllDental_Part00'[IN_Implants], "IN ImplantsClass", 'PSView AllDental_Part00'[IN_ImplantsClass], "IN OralSurgery", 'PSView AllDental_Part00'[IN_OralSurgery], "IN OralSurgeryBony", 'PSView AllDental_Part00'[IN_OralSurgeryBony], "IN OralSurgeryBonyClass", 'PSView AllDental_Part00'[IN_OralSurgeryBonyClass], "IN OralSurgeryBrushBiopsy", 'PSView AllDental_Part00'[IN_OralSurgeryBrushBiopsy], "IN OralSurgeryBrushBiopsyClass", 'PSView AllDental_Part00'[IN_OralSurgeryBrushBiopsyClass], "IN OralSurgeryClass", 'PSView AllDental_Part00'[IN_OralSurgeryClass], "IN OralSurgeryExtractions", 'PSView AllDental_Part00'[IN_OralSurgeryExtractions], "IN OralSurgeryExtractionsClass", 'PSView AllDental_Part00'[IN_OralSurgeryExtractionsClass], "IN OrthoEligibility", 'PSView AllDental_Part00'[IN_OrthoEligibility], "IN PerioMaintenance", 'PSView AllDental_Part00'[IN_PerioMaintenance], "IN PerioMaintenanceClass", 'PSView AllDental_Part00'[IN_PerioMaintenanceClass], "IN PerioNonSurgical", 'PSView AllDental_Part00'[IN_PerioNonSurgical], "IN PerioNonSurgicalClass", 'PSView AllDental_Part00'[IN_PerioNonSurgicalClass], "IN PerioOsseousSurgery", 'PSView AllDental_Part00'[IN_PerioOsseousSurgery], "IN PerioOsseousSurgeryClass", 'PSView AllDental_Part00'[IN_PerioOsseousSurgeryClass], "IN PerioSurgical", 'PSView AllDental_Part00'[IN_PerioSurgical], "IN PerioSurgicalClass", 'PSView AllDental_Part00'[IN_PerioSurgicalClass], "OON AnnualMax", 'PSView AllDental_Part00'[OON_AnnualMax], "OON DeductibleIndiv", 'PSView AllDental_Part00'[OON_DeductibleIndiv], "Posterior Comps", 'PSView AllDental_Part00'[PosteriorComps]),

    TREATAS(ROW("Link", 'tbl_PDPD_INFO'[PLAN_CODE]), 'PSView AllDental_Part00'[Code])))

But I need to add these results to another table called EligibilityFact as I need to use it as a crosswalk table to get the PSView AllDental_Part00 data added to another FACT table.

I was hoping I could use a variable declaration to accomplish this such as below:

AllDentalMerge =

    Var PDPDAllDentalMerge = GENERATEALL(

            tbl_PDPD_INFO,

                CALCULATETABLE(

                    SELECTCOLUMNS(

                        'PSView AllDental_Part00',

                            "IN AnnualMax", 'PSView AllDental_Part00'[IN_AnnualMax], "IN ClassI", 'PSView AllDental_Part00'[IN_ClassI], "IN_ClassII", 'PSView AllDental_Part00'[IN_ClassII], "IN ClassIII", 'PSView AllDental_Part00'[IN_ClassIII], "IN ClassIV", 'PSView AllDental_Part00'[IN_ClassIV], "IN DeductibleIndiv", 'PSView AllDental_Part00'[IN_DeductibleIndiv], "IN Endo", 'PSView AllDental_Part00'[IN_Endo], "IN EndoClass", 'PSView AllDental_Part00'[IN_EndoClass], "IN EndoPulpotomy", 'PSView AllDental_Part00'[IN_EndoPulpotomy], "IN EndoPulpotomyClass", 'PSView AllDental_Part00'[IN_EndoPulpotomyClass], "IN Implants", 'PSView AllDental_Part00'[IN_Implants], "IN ImplantsClass", 'PSView AllDental_Part00'[IN_ImplantsClass], "IN OralSurgery", 'PSView AllDental_Part00'[IN_OralSurgery], "IN OralSurgeryBony", 'PSView AllDental_Part00'[IN_OralSurgeryBony], "IN OralSurgeryBonyClass", 'PSView AllDental_Part00'[IN_OralSurgeryBonyClass], "IN OralSurgeryBrushBiopsy", 'PSView AllDental_Part00'[IN_OralSurgeryBrushBiopsy], "IN OralSurgeryBrushBiopsyClass", 'PSView AllDental_Part00'[IN_OralSurgeryBrushBiopsyClass], "IN OralSurgeryClass", 'PSView AllDental_Part00'[IN_OralSurgeryClass], "IN OralSurgeryExtractions", 'PSView AllDental_Part00'[IN_OralSurgeryExtractions], "IN OralSurgeryExtractionsClass", 'PSView AllDental_Part00'[IN_OralSurgeryExtractionsClass], "IN OrthoEligibility", 'PSView AllDental_Part00'[IN_OrthoEligibility], "IN PerioMaintenance", 'PSView AllDental_Part00'[IN_PerioMaintenance], "IN PerioMaintenanceClass", 'PSView AllDental_Part00'[IN_PerioMaintenanceClass], "IN PerioNonSurgical", 'PSView AllDental_Part00'[IN_PerioNonSurgical], "IN PerioNonSurgicalClass", 'PSView AllDental_Part00'[IN_PerioNonSurgicalClass], "IN PerioOsseousSurgery", 'PSView AllDental_Part00'[IN_PerioOsseousSurgery], "IN PerioOsseousSurgeryClass", 'PSView AllDental_Part00'[IN_PerioOsseousSurgeryClass], "IN PerioSurgical", 'PSView AllDental_Part00'[IN_PerioSurgical], "IN PerioSurgicalClass", 'PSView AllDental_Part00'[IN_PerioSurgicalClass], "OON AnnualMax", 'PSView AllDental_Part00'[OON_AnnualMax], "OON DeductibleIndiv", 'PSView AllDental_Part00'[OON_DeductibleIndiv], "Posterior Comps", 'PSView AllDental_Part00'[PosteriorComps]),

    TREATAS(ROW("Link", 'tbl_PDPD_INFO'[PLAN_CODE]), 'PSView AllDental_Part00'[Code])))

Return

GENERATEALL( 

            'tbl_ELIG_FACT',

                CALCULATETABLE(

                    SELECTCOLUMNS(

                        PDPDAllDentalMerge,

But the results from the variable are not available to be brought into my calculation.

My question is if there is a way to write the "GENERATEALL" and "SELECTCOLUMNS" strategy above in a nested format to merge multiple tables all in the same "New Table" function.

Thanks!

[Stachu]

why you cannot create the calculated tables in Power Query? Is it technical constraint, or a legacy setup?

This problem would be much easier in PowerQuery, you technically can do it in DAX, but I honestly believe that you shouldn't

[Anonymous]

The reason is because of performance issues.  I am working with very unresponsive SQL tables and when attempting to merge tables, group by and create calculated columns I would often have to wait an hour for the program to finish each operation while using DAX to perform the same operation it took 5 minutes.  Due to the complexity of the relationship I need to build between the tables, Power Query bogged down my report too much.