Forum Discussion
Filter with multiple relationship
Doublecheck the data type on GradeValue.Value and make sure that it's still INT.
If it's int take a look in the visualizations pane and make sure that "Sum" is selected on the settings for GradeValue.Value.
This is definitly an issue with dataformatting of some sort. Power BI defaults to aggregation when you have numeric columns so you should never have a scenario like the one you're describing!
No worries! Let me know if it works out.
Hi tex628 ,
It works, actually GradeValue and Grade has a many-to-one relationship because in the GradeValue table, multiple rows is related to one row in Grade. When I fixed it then it works as expected. So in my report I have:
- Table 1 with multiple Student
- Table 2 with multiple University
- Table 3 with Grade.type, Grade.description and GradeValue.value
When I select one specific student, only its corresponding grade will show in table 3, which is what I want. However, when I don't select a university, or a student, table 3 shows all rows, and therefore there will be a lot of redundancy of type and description. Do you know how I can make it so that if nothing is selected, I want to only show the description and type but no value?
- This means if I select one university, I want to show the total grade and its corresponding type and description (works but redundant rows, multiple descriptions, type etc).
- If I select one student, I want to show grades associated with this specific student (works as expected).
- If I don't select anything, no values are shown except for grade type and description.
I suspect I have to do custom measures or DAX, as I am fairly new to PowerBI, if that's the case could you refer me to websites where I can learn that?
Thanks!
Thank you, that was it! I thought it was an int but my migration specified it as a varchar. Now it works as intended! Thanks for the help! Marked as solution.
Do you happen to have the fk_grade_value_id included in your 3rd table visual?
At the moment no, but most likely I can have it if I tweak my database. Would that help if that's the case?
Nevermind that last post,
It should be the the Gradevalue.value column that is causing the duplicate values since it can occur more than once for each row in Grade.
But it still shouldn't display complete duplicates in a table... are the Gradevalue.value also duplicate values or is it only Type/Description?Another question, what dataformat are the gradevalue.value in?
GradeValue.value can have duplicate values as multiple students can have the same grade.
One thing I noticed is that Table 3 which has following columns:
- GradeValue.Value (int)
- Grade.Type (varchar)
- Grade.Description (varchar)
If NOTHING is selected, I get duplicate of type/description but NOT value. However, if I have add in fk_student_id from GradeValue table, then I get duplicate values of both type, description AND value.
Example 1:
Table 3 with only type, description, value, the result when nothing is selected:
Description | Type | Value
duplicate | duplicate | 0
duplicate | duplicate | 1
duplicate | duplicate | 10
Example 2:
Table 3 with type, description, value, AND fk_student_id, the result when nothing is selected:
Description | Type | Value
duplicate | duplicate | 0
duplicate | duplicate | 0
duplicate | duplicate | 0
duplicate | duplicate | 1
duplicate | duplicate | 1
duplicate | duplicate | 10
duplicate | duplicate | 10
What I ultimately want (when nothing is selected):
Description | Type | Value
text1 | text2 | total sum of values
text3 | text4 | total sum of values
Doublecheck the data type on GradeValue.Value and make sure that it's still INT.
If it's int take a look in the visualizations pane and make sure that "Sum" is selected on the settings for GradeValue.Value.
This is definitly an issue with dataformatting of some sort. Power BI defaults to aggregation when you have numeric columns so you should never have a scenario like the one you're describing!Glad to hear it!
When it comes to calculated measures and columns i would simply recommend misrosoft's own help site:
https://docs.microsoft.com/en-us/dax
It's atleast what i use when i need to look something up :-)
Br,
Johannes