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
koorosh
Post Partisan
Post Partisan

Junction table

Hi,
The tables in the attached file have the following relationships:
‘Users’[Useremail] one to many ‘Usersphoneumber’[Useremail]
‘Simcards’[Title] one to many ‘Usersphoneumber’[Title]
‘Simcards’[Title] one to many ‘Phones’[PhoneNumber]

 

I want a table visual including 3 columns from the Phones table (Phonenumber, Title, serialnumber) along with a slicer on the page including Useremail from the Users table so that this slicer can filter the table visual.

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You can create a measure like

Phone is visible = IF ( SELECTEDVALUE(Phones[PhoneNumber]) IN VALUES(UserPhoneNumber[PhoneNumber]), 1 )

and use that as a visual level filter to only show when the value is 1.

 

View solution in original post

6 REPLIES 6
DemoFour
Responsive Resident
Responsive Resident

@koorosh 

If you structure the model to have all information on the user, phone number and email in a table with a key, then this is your customer dimension.  Once you have this joined the the fact table about Phones, your output will work. 

The relationship will be a 1 to Many with much more data and proper keys, as currently there is a 1 - 1 relationship with the source test data.  You will then be able to filter your fact by the dimension. 

Model.jpg


 

Slicer.jpgSlicer 1.jpg

Your DAX will also be much simpler in writing with this type of model, other wise you are just building up technical debt! 

Hi DemoFour, Thank you for your comment please give me time to review it.

johnt75
Super User
Super User

You can create a measure like

Phone is visible = IF ( SELECTEDVALUE(Phones[PhoneNumber]) IN VALUES(UserPhoneNumber[PhoneNumber]), 1 )

and use that as a visual level filter to only show when the value is 1.

 

Thanks. But I can not understand. Could you please explain when the slicer includes email addresses from the 'users' table, what does SELECTEDVALUE(Phones[PhoneNumber]) select when we choose one email address in the slicer?
Please check the attached image.

When you put the phone number in a visual with this measure as a filter SELECTEDVALUE will refer to the phone number on the current row of the visual. The measure will then determine whether to show that row or not.

DemoFour
Responsive Resident
Responsive Resident

HI @koorosh ,

I would change the structure of your tables and have a User table which is a combination of UserPhoneNumber and User, and then just use this dimension as your slicer.  This will then show each phone by user.

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn  this will help you to see what is needed in a star schema to get you going. 


 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors