Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[William Rodriguez]

Greetings PUG community!

How do I reference data model attributes from inside a query that broke its lineage? 

Thoughts:

• I know possible solutions include joining the tables at ETL, restructuring the DAX query so that lineage isn’t broken, etc. My objective is to find if someone has figured how to reference the in-memory table directly from the query (see below)

QUERY:

EVALUATE
GENERATE(
    SUMMARIZE (
        VAR VariableTableOne= 
            SUMMARIZE (
                TABLEONE, 
                TABLEONE[COMMUNITY], 
                TABLEONE[HOUSENUMBER], 
                TABLEONE[OPTIONCODE]
            )
        VAR VariableTableTwo= 
            SUMMARIZE (
                TABLETWO, 
                TABLETWO[COMMUNITY], 
                TABLETWO[HOUSENUMBER], 
                TABLETWO[OPTIONCODE]
            )
        RETURN
            UNION ( VariableTableOne, VariableTableTwo), 
        [COMMUNITY], 
        [HOUSENUMBER], 
        [OPTIONCODE]
    ) , 
    ROW ( 
        “LOCATION” , 
            CALCULATE( 
                FIRSTNONBLANK( TABLEONE[LOCATION] , 1 ) , 

 //VIRTUAL RELATIONSHIP OPTION ONE ( IF THERE WAS LINEAGE, THE FOLLOWING VIRTUAL RELATIONSHIP WOULD TIE THE QUERY TO THE DATA MODEL)
//THE PROBLEM IS THAT ‘VALUES’ NEEDS TO HAVE A FULLY QUALIFIED COLUMN… I WANT TO DIRECT IT TOWARD THE IN-MEMORY TABLE I AM CURRENTLY BUILDING
//                INTERSECT(
//                    VALUES ( TABLEONE[COMMUNITY] ) , 
//                    VALUES( [COMMUNITY] )
//                ), 
//                INTERSECT(
//                    VALUES ( TABLEONE[HOUSENUMBER] ) , 
//                    VALUES ( [HOUSENUMBER] )
//                ), 
//                INTERSECT(
//                    VALUES ( TABLEONE[OPTIONCODE] ) , 
//                    VALUES ( [OPTIONCODE] )
//                )
 
//VIRTUAL RELATIONSHIP OPTION TWO ( IF THERE WAS LINEAGE, THE FOLLOWING VIRTUAL RELATIONSHIP WOULD TIE THE QUERY TO THE DATA MODEL)
//                FILTER(
//                    TABLEONE , 
//                    TABLEONE[COMMUNITY] = [COMMUNITY] &&
//                    TABLEONE[HOUSENUMBER] = [HOUSENUMBER] &&
//                    TABLEONE[OPTIONCODE] = [OPTIONCODE]
//                )                
                
            )  
   )
)
                
ORDER BY [COMMUNITY] , [HOUSENUMBER] , [OPTIONCODE]    

#DAX?

——————————
William Rodriguez
Business Analyst
——————————