Refer to calculated table

[MariekevdAkker]

I use a live connection to a SSAS Tabular Model. Therefor I cannot use calculated columns.

The table factDataExport contains a row for each answer in a form. 
Each participant can have multiple baseline forms with a visit_date. The measure below calculates the number of baselines in each age group. Agegroups are defined in the AddCategories table. 

In the measure below I would like to replace TODAY() with the birth_year of the particpiant. These are available in the 'Birth_year_table' and can be related to the factDataExport by [Record_Name_GUID]. However I can't get the measure to refer to the Birth_Year_table. 

I need the age of the participant as "visit_date" - "Birth_Year" wich are both stored on different rows in the same (factDataExport) table. 

AgeGroups BL = 


var VarCode = SELECTEDVALUE('AddCategories'[QNAME])
var MinAge = MIN('AddCategories'[From])
var MaxAge = MIN('AddCategories'[To])


VAR BL_Form_Instance_ID =          
    CALCULATETABLE(
        VALUES(factDataExport[Form_Instance_ID])
        ,ALL(dimMetadata)
        ,dimMetadata[Field_Variable_Name]="Visit_Type"
        ,factDataExport[AnswerNumeric]=0 --=BL visit
        ,factDataExport[Report_Status] <> BLANK()
    )
  

VAR Birth_Year_table =
		ADDCOLUMNS(
            //Participant IDs
			CALCULATETABLE(VALUES(factDataExport[Record_Name_GUID]))
            // Birth_Year
			,"Birth_Year"
			,CALCULATE(
                MAX(factDataExport[AnswerNumeric])
                ,dimMetadata[Field_Variable_Name]="Birth_Year"
                ,ALL(dimMetadata)
                ,factDataExport[AnswerNumeric] <> BLANK()
            )
		)


Return
    CALCULATE(
        COUNTROWS(factDataExport)
        ,dimMetadata[Field_Variable_Name] = "Visit_Date"
        ,ALL(dimMetadata)
        ,TREATAS(BL_Form_Instance_ID,factDataExport[Form_Instance_ID])  --BL Visits
        ,INT ( YEARFRAC ( TODAY(), factDataExport[AnswerDate], 1 ) ) > MinAge
        ,INT ( YEARFRAC ( TODAY(), factDataExport[AnswerDate], 1 ) ) < MaxAge
        
    )
 

[Anonymous]

Hi @MariekevdAkker ,

You can update the formula of measure [AgeGroups BL] as below and check if it can return your expected result...

AgeGroups BL =
VAR VarCode =
    SELECTEDVALUE ( 'AddCategories'[QNAME] )
VAR MinAge =
    MIN ( 'AddCategories'[From] )
VAR MaxAge =
    MIN ( 'AddCategories'[To] )
VAR BL_Form_Instance_ID =
    CALCULATETABLE (
        VALUES ( factDataExport[Form_Instance_ID] ),
        ALL ( dimMetadata ),
        dimMetadata[Field_Variable_Name] = "Visit_Type",
        factDataExport[AnswerNumeric] = 0 --=BL visit
        ,
        factDataExport[Report_Status] <> BLANK ()
    )
VAR Birth_Year_table =
    ADDCOLUMNS (
        //Participant IDs
        CALCULATETABLE ( VALUES ( factDataExport[Record_Name_GUID] ) ) // Birth_Year
        ,
        "Birth_Year",
            CALCULATE (
                MAX ( factDataExport[AnswerNumeric] ),
                dimMetadata[Field_Variable_Name] = "Birth_Year",
                ALL ( dimMetadata ),
                factDataExport[AnswerNumeric] <> BLANK ()
            )
    )
RETURN
    CALCULATE (
        COUNTROWS ( factDataExport ),
        dimMetadata[Field_Variable_Name] = "Visit_Date",
        ALL ( dimMetadata ),
        TREATAS ( BL_Form_Instance_ID, factDataExport[Form_Instance_ID] ) --BL Visits
        ,
        INT (
            YEARFRAC (
                MAXX (
                    FILTER (
                        Birth_Year_table,
                        [Record_Name_GUID] = factDataExport[Record_Name_GUID]
                    ),
                    [Birth_Year]
                ),
                factDataExport[AnswerDate],
                1
            )
        ) > MinAge,
        INT (
            YEARFRAC (
                MAXX (
                    FILTER (
                        Birth_Year_table,
                        [Record_Name_GUID] = factDataExport[Record_Name_GUID]
                    ),
                    [Birth_Year]
                ),
                factDataExport[AnswerDate],
                1
            )
        ) < MaxAge
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[MariekevdAkker]

Thank you for your answer, I get the following error:
"Calculation error in measure 'MeasureTable'[AgeGroups BL]: A function 'MAXX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

@Anonymous I have uploaded a pbx here: Dropbox 

[Anonymous]

Hi @MariekevdAkker ,

I tried to update the formula of the measure [AgeGroups BL] using the formula in my previous post, it works welll. Please find the details in the screenshot below. Is there anything else I missing here?

Best Regards

[Marieke2 · Accepted solution]

Hi @Anonymous,

This is long overdue but I finally found the solution. 


The measure that eventually worked is:

AGE GROUPS per BIODAYMED = 
VAR x =
CALCULATETABLE (
        ADDCOLUMNS(
            SUMMARIZE (
                factAnswer,
                factAnswer[Participant_GUID],
                factAnswer[Form_Instance_ID]
            )
            ,"BirthYear",
                    DATE ( CALCULATE (
                        MIN ( factAnswer[AnswerNumeric] ),
                        dimMetadata[Field_Variable_Name] = "Birth_Year",
                        ALL ( factAnswer[Form_Instance_ID] )
                    ), 1, 1 ),
                "VisitDate",
                    CALCULATE (
                        MIN ( factAnswer[AnswerDateDate] ),
                        dimMetadata[Field_Variable_Name] = "Visit_Date"
                    ),
                "VisitType",
                    CALCULATE (
                        MIN ( factAnswer[Answer] ),
                        dimMetadata[Field_Variable_Name] = "Visit_Type"
                    ),
                "AgeAtVisit",
                    DATEDIFF (
                        DATE ( CALCULATE (
                            MIN ( factAnswer[AnswerNumeric] ),
                            dimMetadata[Field_Variable_Name] = "Birth_Year",
                            ALL ( factAnswer[Form_Instance_ID] )
                        ), 1, 1 ),
                        CALCULATE (
                            MIN ( factAnswer[AnswerDateDate] ),
                            dimMetadata[Field_Variable_Name] = "Visit_Date"
                        ),
                        YEAR
                    ),
                "Medication",
                    CALCULATE (
                        MIN ( factAnswer[Answer] ),
                        dimMetadata[Field_Variable_Name] = "BioDayMed_Type"
                    )
            )
            
            )


    VAR MinAge = SELECTEDVALUE(AddCategories[From],0.01)
    VAR MaxAge = SELECTEDVALUE(AddCategories[To],150)
    VAR Med = CONVERT(SELECTEDVALUE(WhatIf[Value]),STRING)
    VAR VisitType = "0"

    VAR CountMedAgeGroup = COUNTX (
            FILTER (x, [AgeAtVisit]>=MinAge && [AgeAtVisit]<=MaxAge && [Medication] = Med && [VisitType] = VisitType ),
            [Form_Instance_ID]
        )

   
   VAR NoSelectedMed = COUNTX (
            FILTER (x, [AgeAtVisit]>=MinAge && [AgeAtVisit]<=MaxAge && [VisitType] = VisitType ),
            [Form_Instance_ID]
        )

        
    RETURN
IF(Med=BLANK(), NoSelectedMed, CountMedAgeGroup)

[Anonymous]

Hi @Marieke2 ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

Best Regards

[MariekevdAkker]

Hi @Anonymous ,

Thank you again for your time to help me. 
In the example dataset I get the same results as you, however it should not reply only adults (18+), there are children in the selection. It apears to ignore the filter and uses the overall MAX of all the birth_years.

Furthermore, If I use the exact same measure on my actual model which has a live connection to an analysis server I get the following error:
"Calculation error in measure 'MeasureTable'[AgeGroups BL]: A function 'MAXX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I can also generate the error in the example dataset by adding a calculate around the MAXX function. 

Kind Regards,

Marieke