Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
The selections in the slicer doesn't affect a calculated column. As with calculated table, it gets updated only when:
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.
Proud to be a Super User!
Thank you, I'll give it a try!
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...
Proud to be a Super User!
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:
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |