Forum Discussion

mostof's avatar
mostof
Regular Visitor
3 years ago
Solved

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....
Need Help
  • danextian's avatar
    danextian
    3 years ago

    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.

     

danextian's avatar
danextian
Super User
3 years ago

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 

  • mostof's avatar
    mostof
    Regular Visitor
    3 years ago

    Hi danextian ,

     

    Thanks for your reply. This is what my data looks like:

     

    Computers table:

    UserNamePC_IDPC_Purpose
    UserAPC1Principal Use
    UserAPC2Secondary Office
    UserBPC3Principal Use
    UserCPC4Dedicated Tool
    UserDPC5Kiosk

     

    Users table:

    UserNameFirstNameLastNamePCCount (Calculated Column)
    UserAUserA2
    UserBUserB1
    UserCUserC0
    UserDUserD0

     

    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's avatar
      danextian
      Super User
      3 years ago

      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.