topic Re: Counting occurences of values within a continuous range specified by limits in Desktop
https://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2454321#M876429
<P>Thank you so much !</P><P>Note that there is a parenthesis missing so that the code that works is:</P><P> </P><LI-CODE lang="markup">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
)</LI-CODE><P> </P><P>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.</P>Wed, 13 Apr 2022 09:31:51 GMTvquentin2022-04-13T09:31:51ZCounting occurences of values within a continuous range specified by limits
https://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2447239#M874401
<P>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.</P><P> </P><P>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: </P><DIV><DIV><DIV><SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>CALCULATETABLE</SPAN><SPAN>(</SPAN><SPAN>'units'</SPAN><SPAN>, </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN><SPAN>'radios'</SPAN><SPAN>, </SPAN><SPAN>[frequency_max]</SPAN><SPAN>>=</SPAN><SPAN>2400</SPAN><SPAN> && </SPAN><SPAN>[frequency_min]</SPAN><SPAN><=</SPAN><SPAN>2400</SPAN><SPAN>)), </SPAN><SPAN>[units_sold_num]</SPAN><SPAN>)</SPAN></DIV></DIV></DIV><P>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 <span class="lia-unicode-emoji" title=":winking_face:">ðŸ˜‰</span></P><P> </P><P>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 !</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="vquentin_0-1649506735064.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/697386i7F49797D3B051E60/image-size/medium?v=v2&px=400" role="button" title="vquentin_0-1649506735064.png" alt="vquentin_0-1649506735064.png" /></span></P><TABLE><TBODY><TR><TD>model_id</TD><TD>mfr_country</TD><TD>units_sold_num</TD></TR><TR><TD>1</TD><TD>US</TD><TD>5045</TD></TR><TR><TD>2</TD><TD>US</TD><TD>354</TD></TR><TR><TD>3</TD><TD>JP</TD><TD>26</TD></TR><TR><TD>4</TD><TD>CN</TD><TD>45</TD></TR></TBODY></TABLE><P> </P><TABLE><TBODY><TR><TD>radio_id</TD><TD>model_id</TD><TD>frequency_min</TD><TD>frequency_max</TD></TR><TR><TD>1</TD><TD>1</TD><TD>2400</TD><TD>2450</TD></TR><TR><TD>2</TD><TD>1</TD><TD>2450</TD><TD>2480,5</TD></TR><TR><TD>3</TD><TD>2</TD><TD>2470</TD><TD>2500</TD></TR><TR><TD>4</TD><TD>3</TD><TD>2350</TD><TD>2390,5</TD></TR><TR><TD>5</TD><TD>3</TD><TD>2400</TD><TD>2480</TD></TR><TR><TD>6</TD><TD>4</TD><TD>2400</TD><TD>2440</TD></TR><TR><TD>7</TD><TD>4</TD><TD>2400</TD><TD>2432</TD></TR><TR><TD>8</TD><TD>4</TD><TD>2400</TD><TD>2500</TD></TR></TBODY></TABLE><P> </P><P>The data I want as outcome:</P><TABLE><TBODY><TR><TD>freq</TD><TD>units sold total</TD></TR><TR><TD>2000</TD><TD>0</TD></TR><TR><TD>2400</TD><TD>5116</TD></TR><TR><TD>2470</TD><TD>5470</TD></TR><TR><TD>2500</TD><TD>380</TD></TR><TR><TD>2800</TD><TD>0</TD></TR></TBODY></TABLE><P>Desired outcome as graph:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="vquentin_1-1649506831579.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/697387iA345ACA41C0D42D6/image-size/medium?v=v2&px=400" role="button" title="vquentin_1-1649506831579.png" alt="vquentin_1-1649506831579.png" /></span></P><P> </P>Sat, 09 Apr 2022 12:25:42 GMThttps://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2447239#M874401vquentin2022-04-09T12:25:42ZRe: Counting occurences of values within a continuous range specified by limits
https://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2448975#M874882
<P>Assuming that you have a table 'Desired freq' which contains the frequencies you're interested in, you could create a measure</P><LI-CODE lang="markup">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
)</LI-CODE>Mon, 11 Apr 2022 09:01:10 GMThttps://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2448975#M874882johnt752022-04-11T09:01:10ZRe: Counting occurences of values within a continuous range specified by limits
https://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2454321#M876429
<P>Thank you so much !</P><P>Note that there is a parenthesis missing so that the code that works is:</P><P> </P><LI-CODE lang="markup">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
)</LI-CODE><P> </P><P>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.</P>Wed, 13 Apr 2022 09:31:51 GMThttps://community.powerbi.com/t5/Desktop/Counting-occurences-of-values-within-a-continuous-range/m-p/2454321#M876429vquentin2022-04-13T09:31:51Z