Using a user provided input threshold to convert a continuous column to a binary column.

[abarwe1887]

I have a dataset that that contains information about zip codes, the most relevant of which the Median household income. Currently, the Rich/Poor has simply been generated in Python beforehand.

My objective is to have the Rich/Poor column generated on the spot, depending on a threshold selected by a user through a slicer or a text box. A row will have Rich in it if its Median household income is above the threshold, and Poor otherwise. Any time the user updates the threshold, the Rich/Poor column must automatically update with the new values.

Is this at all possible in Power BI? So far, I've tried using a parameter that automatically generates the following expressions -

Arizona median household income = GENERATESERIES(50000, 100000, 1000)

Arizona median household income Value = SELECTEDVALUE('Arizona median household income'[Arizona median household income], 90000)

This parameter automatically created a slicer with the specified range. I also tried using io with an added column and measures. However, the new column doesn't update and always uses the default threshold in the slicer; whereas the measure can't be used in the X-axix and Legend fields in my map and stacked column chart visuals.

Any help or alternatives are greatly appreciated.

[Anonymous]

Hi @abarwe1887 

First of all, thanks to Ibendlin, hnguy71 and danextian for their interest in this thread.

hnguy71 and danextian offered insight into this problem in their replies, and it seems like the solution you're looking for. If their replies helped you solve your problem, please consider accepting their replies as a solution, which will make it easier for other users experiencing the same problem to find a solution.

If this does not solve your problem, please provide some sample data to facilitate a better understanding of the problem and provide suggestions.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @abarwe1887 

It seems this is what you need. You will need a disconnected table that contains a column of your measure's expected result which is in the screenbelow are yes and no. And another measure to return the desired value based on disconnected table and numeric parameter. The tutorial and sample pbix files are on YouTube. https://youtu.be/dEuDlcSzk7k?si=q1CRm6Om2otqLVxB 

[abarwe1887]

Hi, @danextian. Thanks for your answer. I also checked out your youtube video. However, will this method also work if there is no such "grouping" table? That is, the table I already have with one row for each zip code is the one to which I want to add the new adjustable Rich/Poor column.

[danextian]

There has to be a grouping table of some sort as a measure has no row context by itself.  While you can add a rich/poor calculated column to your zip code table, it won't be dynamic and will not respond to what-if parameters. Calculated columns update only  upon refresh or its formula  is modified.

[hnguy71 · Accepted solution]

Hi @abarwe1887 

That's definitely achieveable with many different means, but you're looking to use a parameter field so I'll give you a solution using a parameter field.

Create two bucketed measures, 1 for rich, 1 for poor.

Rich = 
VAR _Threshold = [Parameter Value]
VAR _Value = CALCULATETABLE('Table', 'Table'[Median Income] >= _Threshold)
RETURN
SUMX(_Value, [Median Income])

Poor = 
VAR _Threshold = [Parameter Value]
VAR _Value = CALCULATETABLE('Table', 'Table'[Median Income] < _Threshold)
RETURN
SUMX(_Value, [Median Income])

All that's left to do is plug it into your visual for segmentation:

[abarwe1887]

Thanks a ton, @hnguy71 . Your solution helped me out a lot. I used a slightly modified version of the measures you suggested, and was able to produce the following plots -

Poor =

VAR _Threshold = 'Threshold'[Threshold Value]

VAR _Value = CALCULATETABLE(Sheet1,Sheet1[Median household income] <= _Threshold)

RETURN COUNTROWS(_Value)

Quick follow up question: The clustered column chart doesn't show me the size/count of each column unless hovered over but the pie chart does. Is there a way to make the column chart show those? Apparently, it doesn't allow measures to be in the X-axis field.

Thanks again,

Atharva

[lbendlin]

yes, use a grouping table as was suggested a few times.

[lbendlin]

Use a disconnected table with your bucket values.  Then use measures to calculate the bucket memberships.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...