Forum Discussion
Use parameters to create conditional columns
hi Anonymous
You could try this as below:
Step1:
Use What if parameter to create a dynamic value for "65"
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
Step2:
Create a simple Grades table that only contains Grade column
Step3:
Then use adjust the formula that Ashish_Mathur provided.
Measure = VAR _table = ADDCOLUMNS ( Grades, "Lower", IF ( Grades[Grade] = "B", [Parameter Value], SUM(Data[Marks])), "Upper", IF ( Grades[Grade] = "B", 0, [Parameter Value] ) ) RETURN COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( Data[Student] ), "XYZ", CALCULATE ( [Scores], CALCULATETABLE ( VALUES ( Data[Student] ) ), ALLSELECTED () ) ), COUNTROWS ( FILTER ( _table, [Scores]>=[Upper]&&[Scores]<=[Lower] ) ) > 0 ) )here is sample pbix file, please try it.
Regards,
Lin
Thank you all for the replies, appreciate it.
This is a sample data set and the actual data has million records with revenue ranging from 0 to several billion. I need to classify customer dynamically based on their total revenue. I could do this pretty easily in other viz. tools so assuming it could be straightforward in powerBI. All i want to do is add a column whose value is determined by comparing the user provided parameter value to an existing column for every row. Is this possible in powerBI?
When you say "user provided Parameter value" in power bi, you mean the user makes a selection from a parameter table. The question is when you say "dynamically", do you also mean the user should be able to set the range of the parameter. Do you want the user to set the range that should be considered an "A", or do you know the range, and just want the user to be able to select that they want to see "A" values.
The user can enter any value for threshold that he wants. I will use a conditional statement to check the column i wish to check against ( revenue) and assign a value for that new column. In the above example, user can enter any number , my code will check if the marks are less or greater than the number he entered and assign a grade. Hope that explains
From your descriptions, it would seem that you have two tables of data. The million+ record table could be called a Revenue table with columns Name and Revenue. The other could be a Revenue Grade table with columns Grade, Revenue Begin, and Revenue End.
You can add one column to the RevenueTbl, the Grade column. This would have the DAX shared earlier to calculate the Grade from the GradeTbl.
Student Mark =
VAR RowContextRevenue = 'RevenueTbl'[Revenue]RETURN
CALCULATE (
MAX ( 'GradeTbl'[Grade] ),
FILTER (
ALL ( 'GradeTbl' ),
RowContextRevenue >= 'GradeTbl'[RevenueBegin]
&& RowContextRevenue <= 'GradeTbl'[RevenueEnd]
)
)You can then add a table of values showing records from RevenueTbl, and have a slicer for the Revene amount. Users slice their data in Power BI via slicer visualizations. You can have a numeric slicer that allows the user to enter values, and the data on the report will slice according to the chosen value(s). See https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers.
This does not have a data entry field like you describe, but it accomplishes your goal. Does this work?
If you really need an app where someone enters a value and they get the output of a single Grade, you should look at PowerApps.
sorry i am having trouble writing DAX. an example powerBI would be great