Forum Discussion
Custom column based on slicer values
Hello,
I'm new to PowerBI and hope someone can help me with this elementary issue.
I have two tables: Users and Computers with a 1:N relationship. Computers table contains a "Purpose" column. I want a column in the Users table that show how many computers in the "Computer" table are within the purpose selection based on a multi-value slicer. I created a table one-column called PC Purpose that contains all the different purposes a computer can have and the slicer is based on that table. Thanks in advance!
The selections in the slicer doesn't affect a calculated column. As with calculated table, it gets updated only when:
- The related tables are refreshed
- Upon creation
- When the formula is updated.
Instead of calculated column use a measure.
PC Count = CALCULATE ( COUNTROWS ( Computers ), FILTER ( Computers, Computers[PC_Purpose] IN VALUES ( PCPurpose[PC Purpose] ) ) )The measure above will return the same result whether there is a relationship or not between PCPurpose and Computers tables. If there is an existing relationship, you cause simply COUNTROWS the Computers table.
4 Replies
Hi mostof ,
If you want to affect calculated table based on the slicers selection, that is not possible. While you can use a measure to create a table, the tables are updated only upon data refresh, when editing the formula table or the first you create it. If that is not the case, please provide a sample data (which can be copy-pasted) and your expected result so it will be easier for anyone to provide you with an answer. Here's how you can get a faster response - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message
Hi danextian ,
Thanks for your reply. This is what my data looks like:
Computers table:
UserName PC_ID PC_Purpose UserA PC1 Principal Use UserA PC2 Secondary Office UserB PC3 Principal Use UserC PC4 Dedicated Tool UserD PC5 Kiosk Users table:
UserName FirstName LastName PCCount (Calculated Column) UserA User A 2 UserB User B 1 UserC User C 0 UserD User D 0 Relationship between Computers and Users is 1 to many.
The table I created for the slicer is called PCPurpose:
PCPurpose
PC Purpose Dedicated Tool Field Use Kiosk Lab Principal Use Reception Secondary Office Shared Training The formula I came up with, which doesn't work, is:
PCCount =
VAR SelectedPCPurpose = ALLSELECTED(PCPurpose[PC Purpose])
RETURN
COUNTROWS(FILTER(RELATEDTABLE(Computers),Computers[PC_Purpose] IN SelectedPCPurpose))The selections in the slicer doesn't affect a calculated column. As with calculated table, it gets updated only when:
- The related tables are refreshed
- Upon creation
- When the formula is updated.
Instead of calculated column use a measure.
PC Count = CALCULATE ( COUNTROWS ( Computers ), FILTER ( Computers, Computers[PC_Purpose] IN VALUES ( PCPurpose[PC Purpose] ) ) )The measure above will return the same result whether there is a relationship or not between PCPurpose and Computers tables. If there is an existing relationship, you cause simply COUNTROWS the Computers table.
Thank you, I'll give it a try!
