cancel
Showing results for
Did you mean:
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 !

 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:

1 ACCEPTED SOLUTION
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])
)``````
2 REPLIES 2
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])
)``````
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])
)``````

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.

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### 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.

Top Solution Authors
Top Kudoed Authors