Forum Discussion

RyanHare92's avatar
RyanHare92
Icon for Helper I rankHelper I
2 years ago
Solved

Referencing columns created with ADDCOLUMN in CALCULATE

This is something that I have struggled with many times in the past and I still quite wrap my head around how to resolve.

This is what I have put together:

EVALUATE

VAR _Working_Table = 
    ADDCOLUMNS(
        Current_Entries,
        "Concatenation", [centre] & [ass_code] & [com_id],
        "Absent_Ind", RELATED(Current_Active_Marks[absent])
    )

VAR _Summarized_Table = 
    SUMMARIZE(
        _Working_Table,
        [Concatenation]
    )
    
VAR _Added_Columns = 
    ADDCOLUMNS(
        _Summarized_Table,
        "Total_Cands", 
        CALCULATE(
            COUNTROWS(_Working_Table),
            FILTER(
                _Working_Table,
                _Working_Table[Concatenation] = EARLIER([Concatenation])
            )
        ),
        "Absent_Count", 
        CALCULATE(
            COUNTROWS(_Working_Table),
            FILTER(
                _Working_Table,
                _Working_Table[Concatenation] = EARLIER([Concatenation]) &&
                _Working_Table[Absent_Ind] = "A"
            )
        )
    )

VAR _Absent_Count = 
    ADDCOLUMNS(
        _Added_Columns,
        "All_Absent", 
        IF(
            [Total_Cands] = [Absent_Count],
            "Yes",
            "No"
        )
    )

RETURN
    COUNTROWS(
        FILTER(
            _Absent_Count,
            [All_Absent] = "No"
        )
    )


The specific part that I am having an issue with is here:

VAR _Added_Columns = 
    ADDCOLUMNS(
        _Summarized_Table,
        "Total_Cands", 
        CALCULATE(
            COUNTROWS(_Working_Table),
            FILTER(
                _Working_Table,
                _Working_Table[Concatenation] = EARLIER([Concatenation])
            )
        ),
        "Absent_Count", 
        CALCULATE(
            COUNTROWS(_Working_Table),
            FILTER(
                _Working_Table,
                _Working_Table[Concatenation] = EARLIER([Concatenation]) &&
                _Working_Table[Absent_Ind] = "A"
            )
        )
    )


Specifically in the CALCULATE when trying do do the following:

FILTER(
                _Working_Table,
                _Working_Table[Concatenation] = EARLIER([Concatenation])
            )

It appears as if I am not able to reference a column previously created with the ADDCOLUMNS function. 

How can I work around this?




  • RyanHare92's avatar
    RyanHare92
    2 years ago

    I still don't understand how DAX works in this context and how to referenced columns created with ADDCOLUMN, but after rubbing my two brain cells together, I managed to solve my issue by doing the following:

    Expected_Entries = 
VAR _Summarized_Table = 
    SUMMARIZE(
        Current_Entries,
        [centre],
        [ass_code],
        [com_id]
    )

VAR _Final_Table = 
    ADDCOLUMNS(
        _Summarized_Table,
        "All_Absent",
        IF(
            CALCULATE(
                COUNTROWS(Current_Entries),
                Current_Entries[centre] = EARLIER([centre]),
                Current_Entries[ass_code] = EARLIER([ass_code]),
                Current_Entries[com_id] = EARLIER([com_id])
            )
            
            =

            CALCULATE(
                COUNTROWS(Current_Active_Marks),
                Current_Active_Marks[centre] = EARLIER([centre]),
                Current_Active_Marks[ass_code] = EARLIER([ass_code]),
                Current_Active_Marks[com_id] = EARLIER([com_id]),
                Current_Active_Marks[absent] = "A"
            ),
            "Yes",
            "No"
        )
    )

RETURN
    COUNTROWS(
        FILTER(
            _Final_Table,
            [All_Absent] = "No"
        )
    )

1 Reply

  • RyanHare92's avatar
    RyanHare92
    Icon for Helper I rankHelper I
    2 years ago

    I still don't understand how DAX works in this context and how to referenced columns created with ADDCOLUMN, but after rubbing my two brain cells together, I managed to solve my issue by doing the following:

    Expected_Entries = 
VAR _Summarized_Table = 
    SUMMARIZE(
        Current_Entries,
        [centre],
        [ass_code],
        [com_id]
    )

VAR _Final_Table = 
    ADDCOLUMNS(
        _Summarized_Table,
        "All_Absent",
        IF(
            CALCULATE(
                COUNTROWS(Current_Entries),
                Current_Entries[centre] = EARLIER([centre]),
                Current_Entries[ass_code] = EARLIER([ass_code]),
                Current_Entries[com_id] = EARLIER([com_id])
            )
            
            =

            CALCULATE(
                COUNTROWS(Current_Active_Marks),
                Current_Active_Marks[centre] = EARLIER([centre]),
                Current_Active_Marks[ass_code] = EARLIER([ass_code]),
                Current_Active_Marks[com_id] = EARLIER([com_id]),
                Current_Active_Marks[absent] = "A"
            ),
            "Yes",
            "No"
        )
    )

RETURN
    COUNTROWS(
        FILTER(
            _Final_Table,
            [All_Absent] = "No"
        )
    )