Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Give this a try.
Measure 1 =
CALCULATE(
DISTINCTCOUNT( 'Table'[ID] ),
'Table'[Type] = "Type1"
)
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
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
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 & 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.
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.
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
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
Give this a try.
Measure 1 =
CALCULATE(
DISTINCTCOUNT( 'Table'[ID] ),
'Table'[Type] = "Type1"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |