How to hide rows in a matrix without affecting the measure calculation on aggregated data

[apiccolotto]

Hello,

I have a matrix in which I show two measures (Measure1 and Measure2). I want that the rows with measure1 empty are not shown. 

If I add in visual filter a filter like "Measure1 is not null",  the empty rows are hidden, but the aggregated value (Measure2 for the row of A that is 2170 in the example) changes since the row with measure1 blank are not considered. 

I need to hide these rows without affecting the aggregated calculation. Is that possible?

For info:

Measure1 = countrows(Table1)

Measure2 = countrows(Table2)

Table1 and Table2 are linked by the following reletionship.

The filed on the rows of the matrix are from Table3 and table4

Thanks in advance.

[v-xicai]

Hi @apiccolotto   ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @apiccolotto ,

You may delete the relationship between Table1 and Table4, change the Cross filter direction of other relationships from Single to Both, which will take these tables treated as a single table. Then  create a new measure like DAX below. Finally , you may put a filter like "Measure1 is not null" into Visual level filter of Table visual,

Measure2_New

var _table = SUMMARIZE('Table3', 'Table3'[Product Descr (Ord Ln)] ,"_Value", [Measure2] )

return
IF(HASONEVALUE('Table3'[Product Descr (Ord Ln)] ), [ Measure2], SUMX(_table,[_Value]))

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@apiccolotto , create a measure using is filtered or is in scope . and make measure 2 as blank when measure 1 is blank. But use a different measure for the total.

refer, these can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

example for GT

sumx(values(Table[product]),[measure2])

for rows

sumx(values(Table[product]),if(isbalnk([measure1]),blank(),[measure2]))

[apiccolotto]

Hi @amitchandak,

thanks for the reply.

I create Measure3 following your suggest:

Measure3 = IF(ISINSCOPE('Table3'[Product Descr (Ord Ln)]),

sumx(values('Table3'[Product Type (Ord Ln)]),if(ISBLANK([Measure1]),blank(),[Measure2])),

sumx(values('Table3'[Product Type (Ord Ln)]),[Measure2]))

It seems it doesn't work since it returns the same value of measure2.

Thanks in advance.

A.