Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

n-1

Hi,

I have the following T table:

CumPayID | ID2 | OrderID

 

which when displayed as a matrix (row=CumPayID, column=ID2, values=distinctcount(OrderID) returns the following:

=

 

I've defined the following measure OrderCnt_Roll_N: calculate(distinctcount(OrderID),filter(T, CumPayID=ID2)) which returns the diagonal in the above matrix.

I've placed it in below matrix (row=CumPayID, values=OrderCnt_Roll_N):

 

 

I need to add to this matrix, the previous element in the diagonal; say I name it OrderCnt_Roll_N-1. It'd look like:

CumPayID | OrderCnt_Roll_N, OrderCnt_Roll_N-1

0 | 24

1 | 23, 24

2| 20, 23

3 | 21, 20

 

How would you do that?

Best

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    You can create two calculated columns like below:

     

    Discount = calculate(distinctcount(Table1[OrderID]),filter(ALL(Table1), 'Table1'[CumPayID]='Table1'[ID2] && 'Table1'[CumPayID]=EARLIER(Table1[CumPayID])))

     

    PreDiscount = LOOKUPVALUE(Table1[Discount],Table1[CumPayID],'Table1'[CumPayID]-1)

     

    Then drag the column PreDiscount to the matrix, set it as Maximum aggregation. Please download attached .pbix file.

     

    Best Regards,
    Qiuyun Yu

6 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    Hi Anonymous

     

    This should work

     

    OrderCnt_Roll_N-1 = LOOKUPVALUE(Table[OrderID],Table[CumPayID],Table[CumPayID]-1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks GilbertQ. It's not a simple search, i need a distinctcount. Can you help?

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Hi Anonymous

         

        Where do you require the distinct count?

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi Anonymous,

     

    You can create two calculated columns like below:

     

    Discount = calculate(distinctcount(Table1[OrderID]),filter(ALL(Table1), 'Table1'[CumPayID]='Table1'[ID2] && 'Table1'[CumPayID]=EARLIER(Table1[CumPayID])))

     

    PreDiscount = LOOKUPVALUE(Table1[Discount],Table1[CumPayID],'Table1'[CumPayID]-1)

     

    Then drag the column PreDiscount to the matrix, set it as Maximum aggregation. Please download attached .pbix file.

     

    Best Regards,
    Qiuyun Yu