topic Re: slabs based factor lookup in DAX Commands and Tips

slabs based factor lookup

mohsinmasood — Tue, 23 Jul 2024 11:29:37 GMT

Hi i am tring to pull slab based factor from my data

i have total count of 34,635 in one table and slabs on another table now i have to lookup slab rate which is 1.75 based on count of 34,635 which falls under 25001 - 36500.

count will increase every month and every time it should lookup the factor based on total counts

Count Table

Slab rate table

Re: slabs based factor lookup

Irwan — Wed, 24 Jul 2024 02:48:46 GMT

hello mohsinmasood

please check if this accomodate your need.

1. create calculated column for calculating slab

Slab = 
CALCULATE(
    MAX('Table'[Slab]),
    FILTER(
        'Table',
        'Count'[Category]='Table'[Category]&&
        'Count'[Count]>'Table'[Start]&&
        'Count'[Count]<'Table'[End]
    )
)

2. create calculated column for calculating slab rate

Slab Rate =
CALCULATE(
    MAX('Table'[Slab Rate]),
    FILTER(
        'Table',
        'Count'[Category]='Table'[Category]&&
        'Count'[Count]>'Table'[Start]&&
        'Count'[Count]<'Table'[End]
    )
)

Hope this will help you.

Thank you.

Re: slabs based factor lookup

Anonymous — Wed, 24 Jul 2024 03:08:24 GMT

Hi mohsinmasood

For your question, here is the method I provided:

Here's some dummy data

“Count Table”

“Slab rate Table”

Create a measure.

Measure slabs = var _Category = SELECTEDVALUE('Count Table'[Category]) var _count = SELECTEDVALUE('Count Table'[Count]) RETURN CALCULATE( SELECTEDVALUE('Slab rate Table'[Slab Rate]), FILTER( ALL('Slab rate Table'), 'Slab rate Table'[Category] = _Category && 'Slab rate Table'[Start] <= _count && 'Slab rate Table'[End] >= _count ) )

Or create a column.

Column slabs = CALCULATE( SELECTEDVALUE('Slab rate Table'[Slab Rate]), FILTER( ALL('Slab rate Table'), 'Slab rate Table'[Category] = 'Count Table'[Category] && 'Slab rate Table'[Start] <= 'Count Table'[Count] && 'Slab rate Table'[End] >= 'Count Table'[Count] ) )

Here is the result.

Regards,

Nono Chen

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