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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Power BI is mainly a visualization tool, not for lots of data entry via parameters. For an education institution, the grades for all students should be in another database system or at least an Excel document. The grades then typically get pulled into Power BI as a data source rather than parameters.

 

To answer your question, though, you can accomplish your goal in a couple of ways.

 

1. Instead of your range table, create a table with one row per score and mark. For example, if you have scores from 0-100, you would have 101 records, with Scores 0-50 being Mark = A, 51-60 being Mark = B, and so on. This provides the simplest matching for your grades. Now you have a one-to-one match between a record in your Student Marks table and the score that is entered or pulled from the other data source.

 

2. Alternatively, changing Student Marks to have ranges simplifies the coding. So, instead of the pair { A, 50 } in your table, you change this to { A, 50, 59 }, and instead of { B, 60 } you have { B, 60, 69 }. Having the full range with begin and end values makes your calculations easier. Let's assume that you also have a Student Score table. This lists the Student Name and a Score that they received. Now, you can add a Calculated Column to that Student Score table with the following DAX:

 

Student Mark =
CALCULATE (
    MAX ( 'Student Marks'[Mark] ),
    FILTER (
        ALL ( 'Student Marks' ),
        'Student Score'[Score] >= 'Student Marks'[Begin Score]
            && 'Student Score'[Score] <= 'Student Marks'[End Score]
    )
)