Reference same table based on another field

[Steve_M]

Hi, I am new to DAX, and this is a little beyond me for now.

I am trying to write a job costing report in Power BI, but I don't have any access to the data model. I can create measures, but the report is using a Live connection, and I can't make any changes to it.

I have a transaction table (12 million rows) that I need to reference back based on a custom field in the transaction to get the job cost.  The transaction has a job number that I group the revenue by an Item ID.  The transaction includes a custom field that contains a PO Number that I then need to go back into the table to retrieve the costs.

sample data

I used ChatGPT to come up with this which gives a result but is very slow (18 secs) to update the visual.  Any idea on how to do this efficiently?

Job Cost = 
SUMX(
    SUMMARIZE(
        'Transaction Lines',
        'Transaction lines'[Document Number],
        'Transaction lines'[Item Internal ID],
        "AmountSum",
        CALCULATE(
            ('Transaction Lines'[Amount]),
            FILTER(
                ALL('Transaction Lines'),
                'Transaction Lines'[Document Number] = 
                MAXX(
                    VALUES('Transaction Lines'[Logistics Kitting Job Costing PO#]),
                    RIGHT('Transaction Lines'[Logistics Kitting Job Costing PO#], 18)
                )
                && 'Transaction Lines'[Item Internal ID] = MAX('Transaction Lines'[Item Internal ID])      
            )
        )
    ),
    [AmountSum]
)

Sample visual

Thanks

Steve

[VahidDM]

Hello Steve,

I understand that you're trying to optimize your DAX measure for calculating the job cost in Power BI, especially since you're working with a large dataset (12 million rows) and have limitations due to the live connection. Let's work through a more efficient way to achieve your goal.

Understanding the Requirement

From your description:

• Objective: Calculate the job cost by referencing the same transaction table based on a custom field (PO Number).
• Data Structure:
  • Transaction Lines Table:
    • Document Number: Identifies each transaction.
    • Item Internal ID: The item identifier.
    • Amount: The transaction amount.
    • Logistics Kitting Job Costing PO#: Contains the PO Number, from which you extract the rightmost 18 characters.

You need to sum the Amount for transactions where:

1. The Document Number matches the PO Number extracted from Logistics Kitting Job Costing PO#.
2. The Item Internal ID matches.

Issues with the Current Measure

Your current measure is slow because:

• Use of FILTER(ALL('Transaction Lines'), ...): This scans the entire table (12 million rows) for each evaluation, which is computationally intensive.
• Complex Calculations inside Iterators: Using SUMX over SUMMARIZE with nested calculations adds to the processing time.

Optimized Measure

We can optimize the measure by:

• Avoiding Full Table Scans: Use direct column filters in CALCULATE instead of FILTER(ALL(...)).
• Leveraging Variables: Store intermediate values to prevent redundant calculations.
• Minimizing the Use of Iterators: Use measures that can take advantage of query optimizations.

Here's the optimized measure:

Job Cost =
VAR PoNumber =
RIGHT( MAX( 'Transaction Lines'[Logistics Kitting Job Costing PO#] ), 18 )
VAR ItemID =
MAX( 'Transaction Lines'[Item Internal ID] )
RETURN
IF(
NOT ISBLANK( PoNumber ),
CALCULATE(
SUM( 'Transaction Lines'[Amount] ),
'Transaction Lines'[Document Number] = PoNumber,
'Transaction Lines'[Item Internal ID] = ItemID
),
BLANK()
)

Tips

1. Data Types Consistency:

   • Ensure that the data types for Document Number, Logistics Kitting Job Costing PO#, and Item Internal ID are consistent (e.g., all text or all numbers). Mismatched data types can cause performance issues.
   • If necessary, use data type conversion functions like VALUE or FORMAT.

2. Avoid Using RIGHT in Filters if Possible:

   • If the PO Number always has a fixed length, consider pre-processing this in the data source or see if there's a way to access it directly without using RIGHT.
   • Alternatively, you can create a calculated column (if possible) that extracts the PO Number, which would improve performance. However, since you can't modify the data model, this may not be an option.

3. Test Performance:

   • After implementing the optimized measure, test the performance to see if it meets your requirements.
   • Monitor the query performance using tools like DAX Studio or the Performance Analyzer in Power BI to identify any bottlenecks.

4. Consider Using KEEPFILTERS:

   • If you encounter issues with filter context, you might consider wrapping your filters with KEEPFILTERS to ensure they behave as intended.

Job Cost =
VAR PoNumber =
    RIGHT( MAX( 'Transaction Lines'[Logistics Kitting Job Costing PO#] ), 18 )
VAR ItemID =
    MAX( 'Transaction Lines'[Item Internal ID] )
RETURN
IF(
    NOT ISBLANK( PoNumber ),
    CALCULATE(
        SUM( 'Transaction Lines'[Amount] ),
        KEEPFILTERS( 'Transaction Lines'[Document Number] = PoNumber ),
        KEEPFILTERS( 'Transaction Lines'[Item Internal ID] = ItemID )
    ),
    BLANK()
)

• Live Connection Constraints: Since you can't modify the data model, options like creating calculated columns are not available.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[Steve_M]

I still haven't solved this, but I needed to park it for now and move onto another project.

[Steve_M]

Hi Vahid, thanks for your quick and detailed response. This has saved the time, which is now 12 seconds down from 24. The only issue is that the totals aren't correct.  JobCost 2 is a new formula, but the total isn't right, showing 1,700 instead of 7,300.  Any ideas?

[Anonymous]

Hi @Steve_M ,
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[VahidDM]

@Steve_M  No Problem.
Change MAX to SUM in DAX Query and check that again please