SOS HELP ! related or lookupvalue from another table

user_1111 — Sun, 23 Jul 2023 05:43:28 GMT

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 :

Re: SOS HELP ! related or lookupvalue from another table

Anonymous — Wed, 26 Jul 2023 08:41:59 GMT

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.

topic Re: SOS HELP ! related or lookupvalue from another table in DAX Commands and Tips

SOS HELP ! related or lookupvalue from another table

Re: SOS HELP ! related or lookupvalue from another table