Forum Discussion
Anonymous
6 years agoNot applicable
Use parameters to create conditional columns
Hi all, new to powerBI. Can you please help me here? I have two columns Student Marks A 50 B 60 C 70 I need to assign grade based on marks. Say if marks > 65 t...
- 6 years ago
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
v-lili6-msft
6 years agoCommunity Support
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