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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.