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 repo...
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" )

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). 

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        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" )