topic Re: Refer to calculated table in DAX Commands and Tips

Refer to calculated table

MariekevdAkker — Thu, 15 Sep 2022 14:27:31 GMT

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 )

Re: Refer to calculated table

Anonymous — Fri, 16 Sep 2022 05:42:49 GMT

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

Re: Refer to calculated table

MariekevdAkker — Mon, 19 Sep 2022 09:34:24 GMT

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

Re: Refer to calculated table

Anonymous — Wed, 21 Sep 2022 03:23:50 GMT

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

Re: Refer to calculated table

MariekevdAkker — Mon, 26 Sep 2022 08:29:48 GMT

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

Re: Refer to calculated table

Marieke2 — Tue, 04 Jul 2023 09:46:40 GMT

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)

Re: Refer to calculated table

Anonymous — Wed, 05 Jul 2023 01:13:57 GMT

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