Forum Discussion

MariekevdAkker's avatar
MariekevdAkker
Frequent Visitor
3 years ago
Solved

Refer to calculated table

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 b...
DAX
  • Marieke2's avatar
    Marieke2
    3 years ago

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

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's avatar
MariekevdAkker
Frequent Visitor
3 years ago

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

    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's avatar
      Marieke2
      Regular Visitor
      3 years ago

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

        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's avatar
      MariekevdAkker
      Frequent Visitor
      3 years ago

      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