Counting occurences of values within a continuous range specified by limits

[vquentin]

I have two tables. The first table 'units' represents different models of electronic hardware units. The second 'radios' represents radios that are part of units. A given unit may have one or more radios. One radio is associated with at most one unit. Each radio is characterized by a frequency range at which it emits electromagnetic radiation. The range is fully specified by a minimum and maximum frequency, which are real numbers.

I want to graph the number of units sold that emit for given frequencies. I managed to create a measure when I specify one frequency value: 

SUMX(CALCULATETABLE('units', FILTER('radios', [frequency_max]>=2400 && [frequency_min]<=2400)), [units_sold_num])

but not when trying to replace the hardcoded value by several from a column. Note that as a Power BI newbie, I would also appreciate any feedback if the above formula can be improved 😉

Here are dummy values. Ideally, I would want to be able to use filters on the visual, so maybe using a measurement table would not be ideal? Thank you very much for your help !

model_id	mfr_country	units_sold_num
1	US	5045
2	US	354
3	JP	26
4	CN	45

radio_id	model_id	frequency_min	frequency_max
1	1	2400	2450
2	1	2450	2480,5
3	2	2470	2500
4	3	2350	2390,5
5	3	2400	2480
6	4	2400	2440
7	4	2400	2432
8	4	2400	2500

The data I want as outcome:

freq	units sold total
2000	0
2400	5116
2470	5470
2500	380
2800	0

Desired outcome as graph:

[johnt75 · Accepted solution]

Assuming that you have a table 'Desired freq' which contains the frequencies you're interested in, you could create a measure

Num units at freq =
var currentFreq = SELECTEDVALUE( 'Desired freq'[freq])
return CALCULATE( SUMX(radios, RELATED(units[units_sold_num]), 
   radios[frequency_max] >= currentFreq && radios[frequency_min] <= currentFreq
)

[vquentin]

Thank you so much !

Note that there is a parenthesis missing so that the code that works is:

Num units at freq =
var currentFreq = SELECTEDVALUE( 'Desired freq'[freq])
return CALCULATE( SUMX(radios, RELATED(units[units_sold_num])), 
   radios[frequency_max] >= currentFreq && radios[frequency_min] <= currentFreq
)

However, this would sum [units_sold_num] as many times as there are radios satisfying the criteria, whereas we do not want to count units multiple times for a currentFreq value. It's not obvious to me how to avoid this.