Help With Matrix Visual

[MTOnet]

I'm having trouble getting a matrix visual to display what I want.

I have the following data

And I am trying to get the matrix to show the category/subcategory and classification for all items that are Type1.  With the following measure, I am able to get the values inside the matrix, but my totals are not correct and it is not displaying 0 for the blank values.

Measure 1 = 
var result = 
CALCULATE(
    COUNT('Table'[ID]),
    FILTER(
        all('Table'),
         'Table'[Type]="Type1" && max('Table'[Category])='Table'[Category] && MAX('Table'[Classification])='Table'[Classification]))
return
    if(isblank(result),0, result)

Finally, I'm looking to have the matrix only have an expansion if there is more than 1 subcategory for a category.  Is this possible to do?  This is what I am hoping for the visual to show.  

[jdbuchanan71 · Accepted solution]

@MTOnet 

Give this a try.

Measure 1 = 
CALCULATE(
    DISTINCTCOUNT( 'Table'[ID] ),
    'Table'[Type] = "Type1"
)

[v-easonf-msft]

Hi, @MTOnet 

Using the field "type" to create  a slicer  or  use the visual filter pane will be easiler.

For your second question,I‘m afraid that maxtrix does not support the implementation of this feature currenctly.

I would suggest you add it as an idea on Power BI Ideas forum. If there are enough votes, the Product Team will evaluate and take good consideration about it.

Best Regards,
Community Support Team _ Eason

[FrankAT]

i @MTOnet,

here is a slightly different solution to @jdbuchanan71's:

 Regards FrankAT

[MTOnet]

@FrankAT @jdbuchanan71 

FrankAT & jdbuchanan71, thank you for your help.  I was able to get both options working with my data, but I'm still having an issue with blanks.  How can I get then to display 0's instead of blank?  I've tried isblank() along with some other options that have not worked.

I've tried the suggestions in this Post as well as some others but have not had success.

[jdbuchanan71]

@MTOnet 

Like is mentioned in the post you reference, it's not that the result of the measure is blank, it's that the intersection of Cat1 and Medum don't exist because all when the values are from the same table.  

In your data, is the relationship of SubCat to Cat a *:1 meaning that a Category can have many SubCats but a SubCat only belongs to 1 category?  

In order to get the 0 instead of the empty space you will need lookup tables in your model.  Proabaly one for Cat / SubCat and one for Classification.

[MTOnet · Accepted solution]

I was able to solve my issue and get 0's to replace any blank values.  Hopefully this helps someone else, as I couldn't find any other examples dealing with a second level on the matrix, only single level.

As @jdbuchanan71  asked about my data, the Category:Subcategory relationship is Many:Many.  So, a subcategory could show up for multiple Categories and multiple Categories could associated to a subcategory.  So it could be something related to tasks, and an anlysis and confirmation, Review - Duplicate, Verified - Duplicate, Verified - Working.

Here is my solution

• Created a new table with only the Catgegories for Type=Type1
• Created a 2nd Table with all the distinct Category and Subcategory permutations 
• Createst a relationship from Category in the original table to table created in the first step
• Created a realtionship on category for the 2 newly created tables
• Created a Hierarchy of Category/Subcategory
• Added the hierarchy as the rows on my Matrix
• updated the Measure

Relationship

Measure 1 = 
var result =
CALCULATE(
   DISTINCTCOUNT( 'Table'[ID] ),
   'Table'[Type] = "Type1" ,
   'Table'[Subcategory]in values('Table 4'[Subcategory])
)

return
    if(ISBLANK(result) || result=0,0, result+0)

Result

[jdbuchanan71 · Accepted solution]

@MTOnet 

Give this a try.

Measure 1 = 
CALCULATE(
    DISTINCTCOUNT( 'Table'[ID] ),
    'Table'[Type] = "Type1"
)