cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBInewbie21
Helper II
Helper II

Table visual related questions

Hi,

I have 3 tables. Student, Certificate, and Tution. I want to show Student and their certificate info on my report visual (table visual 1) and Tution info related to every student in table visual 2. And have following questions which I need help finding answers.

1) In table 1 - Student 003 doesn't have record but I still want to see their record with staus 'Not appeared'. I used 'Show items with no data' which shows blank cell value but how do I mask 'Not appeared' value in the visual?

2) In table 1 - When I select student 001 it filters only related Tution record in table 2 but doesn't show any records when I select student 003.

3) In table 2 - When I select TutionId 1 it doesn't filter table 1, may be because student -> Tution table in relationship but seting bi-directional arrows also doesn't resolve the issue. Can you please help me with the three issues?

 

PBInewbie21_0-1647234571456.png

 

Thanks for your time!

6 REPLIES 6
truptis
Super User
Super User

Hi @PBInewbie21 ,

1) - Create a column - and write the below code:
Result = IF(ISBLANK(status column), "Not Appeared", values(status column))

For 2) & 3) change the relationship to bi-directional.

For 1 - I added new column on Certification table and pasted the code you sent with some changes 

Result = IF(ISBLANK(Certification[Status]), "Not appeared", values(Certification[Status]))
and dragged the new column on the visual but still doesn't show 'Not appeared' on my visual. Pasting the image to show Result cell is empty for CRM003 also, changing relationships to bi-directional don't solve my problem no.2 (doesn't show Tuition record for CRM003 but if you see second image it works for CRM002)
PBInewbie21_1-1647291295041.png

 

PBInewbie21_0-1647291241525.png

 

Hi @PBInewbie21 ,

 

Please remove sensitive data and provide a sample pbix so that we can help you to solve the problem:

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 


Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for continue for willing to help me. Here is the sample file.

truptis
Super User
Super User

Hi @PBInewbie21 ,
For 1- 

Could you please elaborate on what bases you want to display 'Not Appreared'? 
Is it if the Status column is blank then? If yes, then try the below:
Result = IF(ISBLANK(Status), "Not Appreared", Status)

 

Also, please explain your query 2. Thanks.

Regards,

TruptiS 

@truptis - thanks for your reply. Not sure where do I write the formula you suggested.

I'll explain question 1 and 2 again.

1) I want to do left join for Student and Certificate tables. Show all student's records with certificate.status comun. If I do 'show items with no data' for table 1 then it does show me student 003's record with blank in status column. Instead of blank I want to mask 'Not appeared' value on the table column. How can I do that? 

2) I have two tables and when I click on top Student table, it's related record should show in bottom Tuition table. for example, when I click on CRM001 on top table it does show TuitionID 1's record but when I click on CRM003 it doesn't show any record on bottom table.

3) for third problem, I can see that because relationship is one direction from Student to Tuition (1:many) it is not filtering bottom to top table. But I have heard bi-directional relationships are not good. How can I fix this problem? 

Thanks once again for your help!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.