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 nr Order line Received date Desired Output 23...
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 () )
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] )
    )
  • amuola's avatar
    amuola
    Helper II
    8 years ago

    excellent! Again, thank you so much.

     

    Regards

    Amund