Forum Discussion

amuola's avatar
amuola
Helper II
8 years ago
Solved

Checking values in different rows

Hi,

 

I would like to set up a function that will check whether the same date appears for each of the order numbers in the below table.

Order nrOrder lineReceived dateDesired Output
230047448230047448-119.10.2017False
230047448230047448-225.10.2017False
230047450230047450-113.10.2017True
230047450230047450-213.10.2018True
230047453230047453-118.10.2017True
230047454230047454-118.10.2017True
230047459230047459-117.10.2017True
230047459230047459-217.10.2017True
230047461230047461-124.10.2017False
230047461230047461-224.10.2017False
230047461230047461-324.10.2017False
230047461230047461-430.10.2017False

For example, order number 230047448 has two order lines, received on the 19th and the 25th of October – desired output is “False”. The next order has the same received date on each of the order lines, hence desired output is “True”. If there is only one order line, it should return "True".

 

Appreciate any suggestions on this!

 

Amund

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi amuola

     

    Try this calculated column

     

    Desired Output =
VAR Total_Orders =
    CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Order nr] )
    )
VAR SameDate_Orders =
    CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Order nr], TableName[Received date] )
    )
RETURN
    IF ( Total_Orders = SameDate_Orders, TRUE (), FALSE () )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi amuola

     

    Try this calculated column

     

    Desired Output =
VAR Total_Orders =
    CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Order nr] )
    )
VAR SameDate_Orders =
    CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Order nr], TableName[Received date] )
    )
RETURN
    IF ( Total_Orders = SameDate_Orders, TRUE (), FALSE () )
    • amuola's avatar
      amuola
      Helper II
      8 years ago

      yes, it works, thank you so much! Could you please explain the formula and why this is working?

       

      Regards Amund

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi amuola

         

        Here is how it works

         

        1) ALLEXCEPT ( TableName, TableName[Order nr] )

        filters your table and returns the rows with the same Order Number as Current Row

         

        2) ALLEXCEPT ( TableName, TableName[Order nr], TableName[Received date] )

        filters your table and returns the rows with the same Order Number and same date as Current Row

         

        You can try these in separate columns for learning

         

        Total_Orders =
    CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Order nr] )
    )
        SameDate_Orders =
    CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Order nr], TableName[Received date] )
    )