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
)

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.