How to create PBI report using range columns with an user input field

[skumar0105]

Hi,

I want to create a Power BI report of a dataset having range columns. also i need a functionality to give user input field.

once the user has entered the input in field, the value should match with the ranges mentioned in 04 columns.

if the input of all 04 columns is within the range, then its corresponding value mentioned in 05th column should get reflected.

refer below table:

Col1	Col2	Col3	Col4	Col5
1.5-4.8	0.2-3.0	10-28	180-260	10.6
0.2-3.9	1.0-4.2	12-38	200-400	9.7
1.3-5.2	0.8-3.9	08-26	140-290	12.5
0.8-4.2	1.9-5.8	06-20	160-330	8.8
1.0-5.8	0.8-5.2	15-45	110-550	7.6

e.g. (i am using example of row 3)

if the user input value is 4.8(Col1),3.6(Col2),22(Col3) and 220(Col4) then the corresponding value 12.5 should be reflected.

so far i have created min & max columns from each column and applied the if condition, i have created user input field as well using the What if parameter, but when i enter the values in only single field then the col5 value is getting reflected but entering the values from other columns does not make any changes in col5 value.

all i am getting is a mess!

Any suggestions or approach would be highly appreciable.

Regards

Satish 

[v-jingzhang · Accepted solution]

Hi @skumar0105 

Your example input 4.8(Col1),3.6(Col2),22(Col3) and 220(Col4) meets both row 3 and row 5 in the table. For this scenario that an input may meet multiple rows, which Col5 will you hope to display? Or perhaps you want to display all of them?

Suppose you want to display all the Col5 values that meet the input, you can try my solution as below. 

First I transform the original table into the following format with Power Query Editor. 

Then create the following measure to get the reflected col5 values and concatenate them. If the input only meets one row's range, it will display the single reflected col5 value. 

Measure = 
var _col1 = [Col1_input Value]
var _col2 = [Col2_input Value]
var _col3 = [Col3_input Value]
var _col4 = [Col4_input Value]
var _col1Value = CALCULATETABLE(VALUES('Table'[Col5]), 'Table'[Column]="Col1" && 'Table'[MinValue]<= _col1 && 'Table'[MaxValue] >= _col1)
var _col2Value = CALCULATETABLE(VALUES('Table'[Col5]), 'Table'[Column]="Col2" && 'Table'[MinValue]<= _col2 && 'Table'[MaxValue] >= _col2)
var _col3Value = CALCULATETABLE(VALUES('Table'[Col5]), 'Table'[Column]="Col3" && 'Table'[MinValue]<= _col3 && 'Table'[MaxValue] >= _col3)
var _col4Value = CALCULATETABLE(VALUES('Table'[Col5]), 'Table'[Column]="Col4" && 'Table'[MinValue]<= _col4 && 'Table'[MaxValue] >= _col4)
return
CONCATENATEX(INTERSECT(INTERSECT(INTERSECT(_col1Value,_col2Value),_col3Value),_col4Value), 'Table'[Col5], "; ")

The sample pbix has been attached at bottom. You can download it to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[skumar0105]

Hi @v-jingzhang 

Thanks for support.

It worked for me.

Few more queries on this:

1. What should i change if in case i want to show only the max or min value of Measure.

2. is there any functionalty in Power BI where i can give a blank user input column and user can add the actual value which is set by him after getting the value from Measure column and the input value should get recorded alongwith current Datetime.

Basically i want to do a Set Vs. Actual Comparison along with Date & time of act.

[v-jingzhang]

Hi @skumar0105 

1. For the first question, you can modify the last expression to return max/min value by using MAXX/MINX. 

.............
return
MAXX(INTERSECT(INTERSECT(INTERSECT(_col1Value,_col2Value),_col3Value),_col4Value), 'Table'[Col5])

2. For the second question, do you want to record users' input into the data model along with every input action's datetime? If so, I'm afraid this is not supported. Power BI allows you to query data into the report but doesn't allow you to insert new data back into the same data source. 

Best Regards,
Community Support Team _ Jing

[skumar0105]

Hi @v-jingzhang 

Thanks for reply.

Your solutions worked for me.

Thanks you so much.