Forum Discussion

trabass's avatar
trabass
Frequent Visitor
8 years ago
Solved

DAX Calculation Help - Appending and filtering records

I'm stuck figuring out the dax formula to achieve the following.

 

Have a table of data (Tasks) which I report off.  Been asked to take quarterly snapshots (Tasks Archive) to "lock" the values report in those quarters.  However, data in future quarters comes from the "Tasks Table".

 

I've taken the two tables and appended them together.  Now there are duplicate rows.  My pseudo logic that I'm trying to convert to DAX is as follows:

 

If Tasks Table (Task Finish Date) is less than the Tasks Archive Table (Archive Date) then return Tasks Archive Table Row values Except where Tasks Table has values different Tasks Archive Table Then

report row as exception to be fixed.

 

If Tasks Table (Task Finish Date) is greater than the Tasks Archive Table (Archive Date) then return Tasks Table Row values

 

SourceTasks IDArchive DateTasks Finish DateValueRow to Report (Y / N)
Tasks Archive101/02/201831/01/201810Y
Tasks1 31/01/201810N
Tasks Archive201/02/201825/01/2018 N
Tasks2 25/01/20185Exception
Tasks Archive301/02/201830/03/2018 N
Tasks3 30/03/201810Y
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi trabass

     

    I think there is a way in QUERY Editor to get the SOURCE TABLE name.

     

    Nevertheless...since it appears that Archive Date is Blank for TASKS table... you may add this calculated column to get the source

     

    Source =
IF ( ISBLANK ( MergedTable[Archive Date] ), "Tasks", "Tasks Archive" )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    trabass

     

    Try this calculated column.

     

    Assuming your Table Name is "MergedTable"

     

    Row to Report =
VAR FinishDate_is_Earlier =
    CALCULATE (
        FIRSTNONBLANK ( MergedTable[Tasks Finish Date], 1 ),
        FILTER (
            ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
            MergedTable[Source] = "Tasks Archive"
        )
    )
        < CALCULATE (
            FIRSTNONBLANK ( MergedTable[Archive Date], 1 ),
            FILTER (
                ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
                MergedTable[Source] = "Tasks Archive"
            )
        )
VAR Values_are_same =
    CALCULATE (
        SUM ( MergedTable[Value] ),
        FILTER (
            ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
            MergedTable[Source] = "Tasks Archive"
        )
    )
        = CALCULATE (
            SUM ( MergedTable[Value] ),
            FILTER (
                ALLEXCEPT ( MergedTable, MergedTable[Tasks ID] ),
                MergedTable[Source] = "Tasks"
            )
        )
VAR Exception_Check =
    IF (
        FinishDate_is_Earlier
            && Values_are_same = FALSE ()
            && MergedTable[Source] = "Tasks",
        "Exception"
    )
RETURN
    IF (
        Exception_Check = "Exception",
        "Exception",
        IF (
            FinishDate_is_Earlier && Values_are_same
                && MergedTable[Source] = "Tasks Archive",
            "Y",
            IF (
                FinishDate_is_Earlier = FALSE ()
                    && Values_are_same = FALSE ()
                    && MergedTable[Source] = "Tasks",
                "Y",
                "N"
            )
        )
    )
      • trabass's avatar
        trabass
        Frequent Visitor
        8 years ago

        Hi Zubair_Muhammad

         

        Thank you very much for the reply.  I didn't know about using variables in dax - learning slowly...

         

        I shouldn't of included "Source" as an example column.  Unless when you do an append can you add a column that identifies which table the row of data was appended from?

         

        Working on changing the formula you provided (thnks).