Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Icon for Helper V rankHelper 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
    )

     




    In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.


    SpartaBI Logo

    Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

    Showcase Report – Contoso By SpartaBI

CarlsBerg999's avatar
CarlsBerg999
Icon for Helper V rankHelper 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
Icon for Community Champion rankCommunity 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)
    )

 

 


In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.


SpartaBI Logo

Visit SpartaBI website   Visit SpartaBI Linkdin   Visit SpartaBI Facebook

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