Forum Discussion
Custom column based on slicer values
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.
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.