cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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])
return CALCULATE( SUMX(radios, RELATED(units[units_sold_num]),
radios[frequency_max] >= currentFreq && radios[frequency_min] <= currentFreq
)``````
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])
return CALCULATE( SUMX(radios, RELATED(units[units_sold_num]),
radios[frequency_max] >= currentFreq && radios[frequency_min] <= currentFreq
)``````
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors