Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not 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 then A else B. this 65 needs to be dynamic and come from user input

 

Then I need to have a summary table

 

Grade   total students

A            1

B           2

 

How do i go about doing this ? any help will be appreciated

 

 

Thanks

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community 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

4.JPG

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
    )
)

 

5.JPG

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-lili6-msft
Community Support
Community 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

4.JPG

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
    )
)

 

5.JPG

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

thank you so much. can you also please show how i can dynamically change my grade threshold ?

Hi,

Go to Home > Edit Queries > Click on the Grades Table and double click on Source.  Edit the contents of the Table there.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

okay this appears to be a workaround. I also achieved the same with parameters and conditional columns . Unfortunately it is not user friendly as host and expose the URL and end users wont be able to edit the table.

 

Thank you for the tips.

Anonymous
Not applicable

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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. 

 

Capture.PNG

 

 

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
Not applicable

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

RandyPgh
Resolver III
Resolver III

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]
    )
)

kentyler
Solution Sage
Solution Sage

Consider having 3 technical tables: each holding a range of possible marks for a grade level.

The table for A might hold 50,55,60,65,70,75,80, 85,90

You base a slicer on that table.

the user uses the slicer to select the value they want for "A"

and your DAX code references the technical table to get the value to use to calculate who got an A

same for B and C





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Let me know if you need help writing the DAX code to figure out what grade users get depending on their score.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.