Forum Discussion

MariekevdAkker's avatar
MariekevdAkker
Frequent Visitor
4 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
4 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)