Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Matrix Filtered by Distinct Counts Exports No Data

Greetings,

 

I have attached a simplified PBIX which demonstrates an issue I have run into.  The goal is to have a matrix which lists products worked on by more than one employee, "overlap".  I was able to achieve this in the blue matrix (closest to the bottom).  In order to achieve this I used this measure:

Overlap =
CALCULATE( DISTINCTCOUNT( 'Example Table'[Employee] ) ,
FILTER( 'Example Table' ,
[Σ Hours] <> 0 ) )


and filtered the matrix on the measure having a value greater than 1.

 

The issue is when I go to export the blue matrix to excel, the export contains no data.  This issue is encountered in both desktop and service.  I believe the root of the issue is that the filter is only applied to the rows (products) in the matrix but is applied to both the rows and columns (employees) upon export.  With each employee having a DISTINCTCOUNT( employee) of 1 they are all excluded!

 

I also tried to create a calculated column but that removed the context of the month slicer. 

 

Any help or direction would be greatly appreciated as I have spent nearly half a day working this problem.

 

Thanks

Malcolm

PBIX Located Here 

Sample

  • Anonymous's avatar
    Anonymous
    5 years ago

    Yes, the solution was provied and can be found at the Power BI Link.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    Looks like this topic has already got the solution?

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable

      Yes, the solution was provied and can be found at the Power BI Link.