Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count Value Once in Column

Hello,

 

In the New Sale column I want to identify the first instance of the UnitRefNo, this can be based on the date or first receipt number.  

 

ReceiptNumberReceiptAmountPropertyIDUnitRefNoReceiptDateNew Sale
10257₦ 500,000.003275TR625115/11/2018 00:00New Sale
10258₦ 500,000.003275TR625115/11/2018 00:00New Sale
10259₦ 500,000.003275TR625116/11/2018 00:00 
10260₦ 500,000.003275TR625116/11/2018 00:00 

I saw the following code provided by Zubair_Muhammad however is it returning duplicate values for the 'New Sale' whereas it should just be the first instance.

New Sale =
VAR IDCount =
CALCULATE ( COUNTROWS ( GetReceiptsData ), ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] ) ) = 1
VAR First_Date =
GetReceiptsData[ReceiptDate]
= CALCULATE ( FIRSTDATE( GetReceiptsData[ReceiptDate] ), ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] ) )
RETURN
IF ( OR ( IDCount, First_Date ), "New Sale" )

 

Any help would be appreciated. Thanks in advance. 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    You may add a new value to discriminate if the ReceiptNumber is the first instance per ReceiptDate like below:

     
    New Sale =
VAR IDCount =
    CALCULATE (
        COUNTROWS ( GetReceiptsData ),
        ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
    ) = 1
VAR First_Date =
    GetReceiptsData[ReceiptDate]
        = CALCULATE (
            FIRSTDATE ( GetReceiptsData[ReceiptDate] ),
            ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
        )
VAR First_Instance =
    GetReceiptsData[ReceiptNumber]
        = CALCULATE (
            FIRSTDATE ( GetReceiptsData[ReceiptNumber] ),
            ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
        )
RETURN
    IF ( AND ( OR ( IDCount, First_Date ), First_Instance ), "New Sale" )

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Anonymous ,

     

    You may add a new value to discriminate if the ReceiptNumber is the first instance per ReceiptDate like below:

     
    New Sale =
VAR IDCount =
    CALCULATE (
        COUNTROWS ( GetReceiptsData ),
        ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
    ) = 1
VAR First_Date =
    GetReceiptsData[ReceiptDate]
        = CALCULATE (
            FIRSTDATE ( GetReceiptsData[ReceiptDate] ),
            ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
        )
VAR First_Instance =
    GetReceiptsData[ReceiptNumber]
        = CALCULATE (
            FIRSTDATE ( GetReceiptsData[ReceiptNumber] ),
            ALLEXCEPT ( GetReceiptsData, GetReceiptsData[UnitRefNo] )
        )
RETURN
    IF ( AND ( OR ( IDCount, First_Date ), First_Instance ), "New Sale" )

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.