Custom column based on slicer values

[mostof]

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!

[mostof]

Thank you, I'll give it a try!

[danextian]

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/144... 

[mostof]

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

[danextian · Accepted solution]

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.