Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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:
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:
Solved! Go to Solution.
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
)
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!