Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Merge multiple tables using GENERATEALL

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!
 
Need Help

2 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

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

      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.