Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
  • v-lili6-msft's avatar
    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