Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Comparing Multiple Values against a single Employee in a Table and performing Filtering

Hi, I have two datasets that I have merged with each other. The merged dataset looks like this, 

 

Emp_IDResource_NamePrimary CodeIsProjection
1HarisAFALSE
1HarisATRUE
1HarisBTRUE
2JackBFALSE
2JackBTRUE
3JohnCFALSE
3JohnBTRUE
4AliceBFALSE
4AliceBTRUE
4AliceCFALSE
4AliceC

TRUE

 

One dataset contains analysis of Employees working on Different Projects. It is possible for one employee to be working on multiple projects at the same time (for example, Alice is working on two projects at the same)


Other dataset contains Projections of the employees against different projects. 

 

My goal is to achieve a Tabluar visual that can show me the Employees Data whose projections were not equal to the actual projects. Also, I need to see the Projected Projects and the Submitted Projects against these Employees. For example, for the above dataset, my desired result would be the following table, 

 

 

As you can see, for these two employees, Projected Projects are not equal to the Submitted Projects. For the other two people, projections are equal to the actual submitted projects.

 

I am unable to figure out a way to Filter my complete data in this Visual to only show the employees where Projected Projects are equal to the Submitted Projects. I have managed to create measures that concatenate the projected projects (or submitted projects) for every employee, however. The measures are as given below, 

 

 

Projected Projects = 

var _Selected = SELECTEDVALUE('dataset'[Emp_ID])
var _Result = 
    CONCATENATEX(
        FILTER(
            'dataset', 
            'dataset'[Emp_ID] = _Selected
            &&
            'dataset'[IsProjection]
        ),
         'dataset'[Primary Code],
        ", "
    )

return _Result

 

 

 

Submitted Projects = 
var _Selected = SELECTEDVALUE('dataset'[Emp_ID])
var _Result = 
    CONCATENATEX(
        FILTER(
            'dataset', 
            'dataset'[Emp_ID] = _Selected
            &&
            'dataset'[IsProjection] = FALSE()
        ),
         'dataset'[Primary Code],
        ", "
    )

return _Result

 

 

Can anyone help me out in this problem? Thank you.

 

If Anyone would like to download the Sample Power BI File, you may do so from here: https://drive.google.com/file/d/11hQFrXdHsEYt8z8I5m6ZIf5SebHpMdL-/view?usp=drive_link

  • Hi Anonymous 

    Place the following measure in the filter pane of tge table visual, select"is not blank" then apply the filter 

    FilterMeasure =
    COUNTROWS (
    FILTER (
    VALUES ( 'dataset'[Emp_ID] ),
    [Projected Projects] <> [Submitted Projects]
    )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 

    Place the following measure in the filter pane of tge table visual, select"is not blank" then apply the filter 

    FilterMeasure =
    COUNTROWS (
    FILTER (
    VALUES ( 'dataset'[Emp_ID] ),
    [Projected Projects] <> [Submitted Projects]
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable

      tamerj1 
      Thank you so much! This worked like a charm! 
      I was trying to do this same thing by using simple IF Condition, 

       

      If ([Projected Projects] = [Submitted Projects], TRUE(), FALSE()) 

      But that was just not working for me, for a reason I don't get. Thank you however for providing me a much more effective and a working approach! Really appreciate it.