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

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.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

sorry i am having trouble writing DAX. an example powerBI would be great