Using values to compare from 2 tables in DAX Filter

[MikhailGG]

How to use 2 different tables in filter expression?

CALCULATE (VALUES ( 'Benchmark Categories'[Categories]), Table[Value]>='Benchmark Categories'[Lower_Level], Table[Value]<'Benchmark Categories'[Upper_Level])

doesn't work.

[FreemanZ]

hi @MikhailGG ,

how are Benchmark Categories and 'table' related?

[MikhailGG]

Hi @FreemanZ 

There is no relationship between the tables. Benchmark Categories is customly created.

[FreemanZ]

hi @MikhailGG ,

what filter context do you have for the needed measure? Or how do you plan to visualise the measure?

[MikhailGG]

I have multiple categories in Benchmark Categories table,

Category	Lower_Level	Upper_Level
0-10	0	10
11-20	11	20
21-30	21	30

I want to create a measure that I would use to visualize count of dogs within those ranges. Y-axis is the measure. X-axis is the Count of Occurences. Table[Values] is the value that corresponds to each dog individualy, e.g. it's height in inches.

The chart's output is that 819 dogs are of the height between 0 and 10. 227 dogs are of the height between 11 and 20, and so on.

[Anonymous · Accepted solution]

Hi @MikhailGG ,

Here is my sample data:

You can use this DAX to create a calculated table in table Benchmark Categories

Dog_ID_Count = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Value] >= 'Benchmark Categories'[Lower_Level] &&
        'Table'[Value] <= 'Benchmark Categories'[Upper_Level]
    )
)

And the final output is as below:

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

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached files.

Hope this helps.