SOS HELP ! related or lookupvalue from another table

[user_1111]

Hello everyone! I need some help, colleagues.

I have a fact table showing the quantity by segment and duration of employee tenure, and there is a reference table with norms based on the segment and duration of employee tenure.

How can I use DAX  to fetch the value and correctly distribute the quantity range that meets the norms, and calculate the Percentage of Norm Completion based on the segment and duration of employee tenure? There is no direct relationship - I created an index column for the segment. The norm values are different for each segment.

I am attaching screenshots of the tables for better understanding.

need result an screenshot :

[Anonymous · Accepted solution]

Hi @user_1111 ,

I suggest you to transform your tables first as below.

New Table:

Measure:

Range =
VAR _PART1 =
    CALCULATE (
        MAX ( 'Table'[Norm] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Segment] = MAX ( 'Table (2)'[Segment] )
                && 'Table'[Tenure] = MAX ( 'Table (2)'[Tenure] )
                && 'Table'[Norm] <= MAX ( 'Table (2)'[Norm] )
        )
    )
VAR _VALUE1 =
    CALCULATE (
        SUM ( 'Table'[Percentage] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Segment] = MAX ( 'Table (2)'[Segment] )
                && 'Table'[Tenure] = MAX ( 'Table (2)'[Tenure] )
                && 'Table'[Norm] = _PART1
        )
    )
VAR _PART2 =
    CALCULATE (
        MAX ( 'Table'[Percentage] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Segment] = MAX ( 'Table (2)'[Segment] )
                && 'Table'[Tenure] = MAX ( 'Table (2)'[Tenure] )
                && 'Table'[Norm] > MAX ( 'Table (2)'[Norm] )
        )
    )
RETURN
    IF ( _PART1 = BLANK (), _PART2, _VALUE1 )

Result is as below.

You can download my attatchment to learn more details.

Best Regards,
Rico Zhou

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