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)

     

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