Seeking Assistance with measure filtering

[Anonymous]

Hi there,

I am hoping someone is able to provide some valuable assistance on my problem.

I have a matrix visual as follows:

The first column is a measure with the following formula:

% of Workforce Compliance (Item) = DIVIDE([# of Compliance Users (Assigned an Item)],[# of Users (Assigned an Item)],0)

The second column is as follows:

# of Compliance Users (Assigned an Item) = CALCULATE([# of Users (Assigned an Item)],'Curriculum Status (FACT)'[Item Competency Indicator]="Competent")

Note: [Item Competency Indicator] only returns either "Competent" or "Not Yet Competent"

And the third column is as follows:

# of Users (Assigned an Item) = CALCULATE(count('Curriculum Status (FACT)'[User ID]),ALLSELECTED('Curriculum Status (FACT)'[Item ID]))

The matrix table above is exactly what I need it to look like but without the second 2 columns.  The issue is when I go to remove the second 2 columns (actually, even just the third column will do it), the red icons disappear.

I am fairly sure they disappear because the value is blank and the third column is clearly the only column that keeps them displaying, because there are values against that row.  I have tried many things including converting the blanks to zero, but then I get many more rows returned that I don't need or want.  They are being returned because there are many other user ID's with blank values for this item ID.  All I want to return is the value that contains the calculated percentage (0% - 100%) for only those users that should be in the Item ID.

Any help greatly appreciated!!

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

Have you ever tried show item with no data for rows?

As a workaround, you can hide the column in the matrix that you don't want to show by colsing the word wrap in values and column headers and drag and drop the width of column:

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

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft 

It's a good idea but there is actually many columns that I have in the table..  I just showed the example table with 1 column.   If I do this for all of them, then most of the columns I want to show will be reduced in size and there will be too many columns to manually go through a resize.  I also believe that blank values will no come through even with this selected, although admittedly I haven't tried it.

I feel that must be a way of handling this via the measure, but I cannot come up with an answer.

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to use show item with no data?

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft

I tried the "show data with no data" option and although it didn't completely solve my issue, it allowed me to continue working the problem and I have now worked out how to do what I want.  Thanks so much!!!

Regards.