cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## slabs based factor lookup

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

2 ACCEPTED SOLUTIONS
Solution Specialist

hello @mohsinmasood

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]    ))`

Thank you.

Community Support

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.

2 REPLIES 2
Community Support

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.

Solution Specialist

hello @mohsinmasood

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]    ))`

Thank you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.