Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper V
4 years ago
Solved

Calculated column (CALCULATE + FILTER) from the same table

Hi, 

 

I'm trying to create a calculated column that returns for values based on two adjacent columns in the same table. For some reason, the value that is returned is not correct. I think this might have to do with row context, but i don't quite understand this so well. Basically the code ignores the 2nd filter (Sales Order ID = CurrentRowSO). Why?

 

Project ID = 

VAR Task_ID = MAX('Sales'[Task ID])
VAR CurrentRowSO = 'Sales'[Sales Order ID]

RETURN

CALCULATE(
LEFT(Task_ID,LEN(Task_ID)-
(LEN(Task_ID)-FIND("-",Task_ID)+1)),
    FILTER('Sales','Sales'[Task ID]<>BLANK()),
    FILTER('Sales','Sales'[Sales Order ID]=CurrentRowSO))

  

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    CarlsBerg999 so yes, it's because of your max you did in the beginning. I suspected right 🙂
    Try this:

     

    Project ID =
VAR CurrentRowSO = 'Sales'[Sales Order ID]
VAR Task_ID = MAXX(FILTER('Sales', 'Sales'[Sales Order ID] = CurrentRowSO), 'Sales'[Task ID])

RETURN
    CALCULATE (
        LEFT (
            Task_ID,
            LEN ( Task_ID )
                - (
                    LEN ( Task_ID ) - FIND ( "-", Task_ID ) + 1
                )
        ),
        'Sales'[Task ID] <> BLANK (),
        'Sales'[Sales Order ID] = CurrentRowSO
    )

     







          

    Showcase Report – Contoso By SpartaBI

5 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    CarlsBerg999 I'm not sure what you are trying to calculate and it's strange that you used MAX on the 1st var but maybe that's what you need.
    The max gives you the maximum task of all the rows (as it's being evaluted for a calcaulted column and there for doesn't have any filter context at the point you execute it). Maybe you wanted something else there, let's say the max for that order or something. 
    Anyway try this 1st (didn't touch the max aspect):

     

     

    Project ID =
VAR Task_ID = MAX('Sales'[Task ID])
VAR CurrentRowSO = 'Sales'[Sales Order ID]
RETURN
    CALCULATE (
        LEFT (
            Task_ID,
            LEN ( Task_ID )
                - (
                    LEN ( Task_ID ) - FIND ( "-", Task_ID ) + 1
                )
        ),
        'Sales'[Task ID] <> BLANK (),
        'Sales'[Sales Order ID] = CurrentRowSO
    )

     

     







          

    Showcase Report – Contoso By SpartaBI

    • CarlsBerg999's avatar
      CarlsBerg999
      Helper V
      4 years ago

      This produced the same result as my code (=incorrect). The table below shows a part of the actual table and the hoped outcome. The code above (as well as mine) returned P99, which is incorrect and ignores the filter that says Sales Order ID must be CurrentRowSO. 

       

      Sales Order IDSales Order Line Item IDSO ID & SO Line Item IDItem Cancel IDItem Cancel TextTask IDProject ID
      60620606-201Not Canceled P88
      60610606-101Not CanceledP88-2P88
      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        CarlsBerg999 so yes, it's because of your max you did in the beginning. I suspected right 🙂
        Try this:

         

        Project ID =
VAR CurrentRowSO = 'Sales'[Sales Order ID]
VAR Task_ID = MAXX(FILTER('Sales', 'Sales'[Sales Order ID] = CurrentRowSO), 'Sales'[Task ID])

RETURN
    CALCULATE (
        LEFT (
            Task_ID,
            LEN ( Task_ID )
                - (
                    LEN ( Task_ID ) - FIND ( "-", Task_ID ) + 1
                )
        ),
        'Sales'[Task ID] <> BLANK (),
        'Sales'[Sales Order ID] = CurrentRowSO
    )

         







              

        Showcase Report – Contoso By SpartaBI