cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vquentin
Frequent Visitor

Counting occurences of values within a continuous range specified by limits

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 !

 

vquentin_0-1649506735064.png

model_idmfr_countryunits_sold_num
1US5045
2US354
3JP26
4CN45

 

radio_idmodel_idfrequency_minfrequency_max
1124002450
2124502480,5
3224702500
4323502390,5
5324002480
6424002440
7424002432
8424002500

 

The data I want as outcome:

frequnits sold total
20000
24005116
24705470
2500380
28000

Desired outcome as graph:

vquentin_1-1649506831579.png

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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
Frequent Visitor

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors