Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.