Reference same table based on another field

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