Forum Discussion
Solved
Matrix Visual custom calculation
Hi, from this picture is my sample matrix visual. I want to make a custom calculation for each Category. When put value in matrix table it will sum value of each item automatically. But what if I wa...
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
ISINSCOPE function (DAX) - DAX | Microsoft Learn
expected result measure: = VAR _valuetotal = [value total:] VAR _subcategory2result = CALCULATE ( SUM ( data[Value] ), sub_category[category] = 2 ) + CALCULATE ( SUM ( data[Value] ), sub_category[category] = 3 ) VAR _subcategory3result = CALCULATE ( SUM ( data[Value] ), sub_category[category] = 1 ) - CALCULATE ( SUM ( data[Value] ), sub_category[category] = 2 ) RETURN SWITCH ( TRUE (), ISINSCOPE ( sub_category[sub_category] ), _valuetotal, ISINSCOPE ( sub_category[category] ) && MAX ( sub_category[category] ) = 2, _subcategory2result, ISINSCOPE ( sub_category[category] ) && MAX ( sub_category[category] ) = 3, _subcategory3result )
This solution works. But what if the same sub_category belongs to another category like sub_cat A it refers to category 2.
As far as I know, the relationship will be broken because the sub_category is a primary key here.