Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
CarlsBerg999
Helper V
Helper V

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))

  

1 ACCEPTED SOLUTION

@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
    )

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

5 REPLIES 5
SpartaBI
Community Champion
Community Champion

@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
    )

 

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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

@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
    )

 




2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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
                )
        )

 

@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)
    )

 

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.