Forum Discussion

JS's avatar
JS
Helper II
4 years ago
Solved

Partial filter with concatenate value

Hello all gurus! 

 

Seeking some advise if this is something possible. 

 

Say i have a simple fact table below: 

 

ClientTeamRevenue
Client ATeam A500
Client ATeam B100
Client BTeam A1000

 

I currently have a chart that shows any client with 2 or more team involved in: 

 

The Team involved tooltip is using the concatenatex measure below: 

Team Involved = CONCATENATEX(VALUES(Sales[Team]), Sales[Team], " & ")
To filter the chart with only 2 or more team, i did a Distinctcount on team name, and applied a filter on the visual itself as shown below:

 

 
I have a team slicer, I want to be able to select Team A, and it will show Client A total value together with Team B. So when i am selecting Team A, i know that Team B is also involved with this Client A. 
 
Right now when i select Team A, I am getting this just Team A value, without the involvement of Team B. 
 
Would a measure solve this or I would have to remodel the data that i currently have.
 
Thanks in advance! 
 
JS

 

Need Help
Tips and Tricks
  • isabella's avatar
    isabella
    4 years ago

    Hi JS 

     

    I did a local test，my approach is to create measure，

    One measure is to sum the revenue，the other one is to concatenate the team name

    Measure = CALCULATE(SUM('Table (2)'[Revenue]),FILTER(ALL('Table (2)'),[Client] = MAX('Table (2)'[Client])))
    Measure 2 = calculate(CONCATENATEX(VALUES('Table (2)'[Team]),[Team],"&"),FILTER(ALL('Table (2)'),[Client] = MAX('Table (2)'[Client])))

     

     

     

     

     

     

    Best Regards,

    Community Support Team _Isabella

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    JS ,

    Try like

    Team Involved = calculate( CONCATENATEX(VALUES(Sales[Team]), Sales[Team], " & "), removefilters(Sales[Team]) )

    • JS's avatar
      JS
      Helper II
      4 years ago

      Hello amitchandak 

      Thanks for the prompt response! It did partially solved the problem, however i had missed out one important element. I just edited my post above. 

       

      I filtered the chart to only show 2 teams or more in a specific client. However with that filter count, when i just select Team A on my silcer, the chart is registering as ONE team count, and the chart will not show what is required. 

      But thanks again! 

  • isabella's avatar
    isabella
    Microsoft Employee
    4 years ago

    Hi JS 

     

    I did a local test，my approach is to create measure，

    One measure is to sum the revenue，the other one is to concatenate the team name

    Measure = CALCULATE(SUM('Table (2)'[Revenue]),FILTER(ALL('Table (2)'),[Client] = MAX('Table (2)'[Client])))
    Measure 2 = calculate(CONCATENATEX(VALUES('Table (2)'[Team]),[Team],"&"),FILTER(ALL('Table (2)'),[Client] = MAX('Table (2)'[Client])))

     

     

     

     

     

     

    Best Regards,

    Community Support Team _Isabella

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.