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 mig...
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

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

CarlsBerg999's avatar
CarlsBerg999
Helper V
4 years ago

This works. What are we doing in this code differently. In the variable, the Task_ID apparently iterates through the entire table (which is filtered by the CurrentRowSO), looking for the largest figure. The largest figure is never "blank".

 

--> Do we even need the calculate function? 

Project ID = 
VAR CurrentRowSO = 'ODATA | Biovian | Sales Order Volume Extended'[Sales Order ID]
VAR Task_ID = MAXX(FILTER('ODATA | Biovian | Sales Order Volume Extended','ODATA | Biovian | Sales Order Volume Extended'[Sales Order ID]=CurrentRowSO),'ODATA | Biovian | Sales Order Volume Extended'[Task ID])

RETURN
        LEFT (
            Task_ID,
            LEN ( Task_ID )
                - (
                    LEN ( Task_ID ) - FIND ( "-", Task_ID ) +1
                )
        )

 

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    CarlsBerg999 yep you don't need such a long code in general when you are trying to do something like that. You can also write:

     

     

    ProjectID =
VAR CurrentRowSO = 'Sales'[Sales Order ID]
VAR Task_ID = MAXX(FILTER('Sales', 'Sales'[Sales Order ID] = CurrentRowSO && 'Sales'[Task ID] <> BLANK()), 'Sales'[Task ID])

RETURN
   LEFT (
        Task_ID,
        LEN ( Task_ID )
            - (LEN ( Task_ID ) - FIND ( "-", Task_ID ) + 1)
    )

     

     





          

    P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
    Showcase Report – Contoso By SpartaBI