Forum Discussion

MarcinSz's avatar
MarcinSz
Frequent Visitor
3 years ago
Solved

Filter applied to column in matrix.

Hi Everyone, 

 

I'm trying to find solution for the following problem. 

 

I have a matrix with surveys data. Country and type of answer in columns and question in rows. As a value I have number of answers/surveys. What I need to do is to create filter which will remove column (country) when the number of answers is lower than 15. My problem is that every time filter always check number of answers for the total matrix. 46 in example below. 

 

The final result I would like to have in the example below is country B will be filtered out from this matrix and only country A and C will be visible. 

 

 

I will be really appreciate for help.

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, MarcinSz ;

    You could create a new table then create a measure.

    1.new table.

    country = VALUES('Table'[Country])

    2.new measure.

    Measure = 
var _totalvalue=CALCULATE( SUM('Table'[Value]),FILTER(ALL('Table'), [Country]=MAX('country'[Country])&&[Question]=MAX('Table'[Question])))
var _result=

IF(ISINSCOPE('Table'[Question]),
IF(_totalvalue>15,CALCULATE( SUM('Table'[Value]),FILTER('Table', [Country]=MAX('country'[Country])))) ,
IF(_totalvalue>15,_totalvalue))
return  IF(HASONEVALUE(country[Country]),_result,CALCULATE( SUM('Table'[Value]),FILTER(ALL('Table'), [Question]=MAX('Table'[Question]))))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, MarcinSz ;

    You could create a new table then create a measure.

    1.new table.

    country = VALUES('Table'[Country])

    2.new measure.

    Measure = 
var _totalvalue=CALCULATE( SUM('Table'[Value]),FILTER(ALL('Table'), [Country]=MAX('country'[Country])&&[Question]=MAX('Table'[Question])))
var _result=

IF(ISINSCOPE('Table'[Question]),
IF(_totalvalue>15,CALCULATE( SUM('Table'[Value]),FILTER('Table', [Country]=MAX('country'[Country])))) ,
IF(_totalvalue>15,_totalvalue))
return  IF(HASONEVALUE(country[Country]),_result,CALCULATE( SUM('Table'[Value]),FILTER(ALL('Table'), [Question]=MAX('Table'[Question]))))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.