Calculate wierd filter behavior

[elaj]

Hi,

i have to show typical weighted survey answer(category) percentages.

I thought i know how to do it and now i get a really confusing result.

Ok i try to explain it:

I have one column with the weight, one column with the answers (Q1_lbl) as "strings" which i want to use as a filter for the percentage measure (Q1 % (W)), to show the percentage of each answer(category). I have one additional column for the sort order for the labels (Q1_lbl_sort).

I need an unweighted "base" to show to my viewers and normally i use this approach:

New Measure:

Q1 % B (UW) = calculate(COUNTA(data[Q1_lbl]), data[Q1_lbl] <> Blank(), all(data[Q1_lbl]))

why data[Q1_lbl] <> BLANK()? because i only want to calculate the persons who have answers.

why "all"? because i want to filter the values by Q1_lbl. The base has to be the same, because for all answers i had the same base.

Now i need a weighted base to calculate my percentage and normally i use this approach:

New Measure:

Q1 % B (W) = calculate(SUM(data[weight]), data[Q1_lbl] <> Blank(), all(data[Q1_lbl]))

why data[Q1_lbl] <> BLANK()? because i only want to calculate the persons who have answers.

why "all"? because i want to filter the values by Q1_lbl. The base has to be the same, because for all answers i had the same base.

Now i need the weighted mentions of my probands. its the same as my weighted base but without the "all" statement, because now i want to have it filtered by my answer(categories) Q1_lbl and normally i use this approach:

New Measure:

Q1 % N (W) = calculate(SUM(data[weight]), data[Q1_lbl] <> BLANK())

why data[Q1_lbl] <> BLANK()? because i only want to calculate the persons who have answers.

calculating the percentage should be easy now:

New Measure:

Q1 % (W) = [Q1 % N (W)] / [Q1 % B (W)]

and now i have a problem. the mentions measure [Q1 % N (W)] (coloured orange in the picture) has the same values in each row. I think it is because i used data[Q1_lbl] <> BLANK(). But i have to use this.. i only want to calculate the people who have an answer in Q1_lbl. And i thought only all(data[Q1_lbl]) should delete filters for this column.

so the first question is:

how can i calculate only people who have answers in Q1_lbl BUT dont destroy the filter of Q1_lbl in the visual?

And how can i filter this to not create values in the red marked row? (without filtering the visual)

And the other weird thing is.. if i now sort Q1_lbl after Q1_lbl_sort. then everything gets filtered after Q1_lbl.

But also not as it should. Row 1 is gone... this is good.. And [Q1 % N (W)] now has the right values. But the two base columns should have the values as in picture one.

Do you have an idea how to solve this? Or am i doing it wrong? is there a better approach?

is there a way to filter something over a column in a calculate statement without destroying additional filters added in the visual out of the same column?

here my file

attached_pbx_file 

thanks for your answers

[AntrikshSharma]

use KEEPFILTERS in your code, even while you think you are removing blank, when you are at the blank row you are returning same result because data[Q1_lbl] <> BLANK () expands into FILTER ( ALL ( data[Q1_lbl] ), data[Q1_lbl] <> BLANK () ) and then even at the blank cell you are returning all the rows in data[Q1_lbl] that are not blank but with KEEPFILTERS you create an INTERSECTION between the original filter and the new filter create by CALCULATE.
Also for future reference you don't need to use ALL in such calculation because there already is a hidden ALL in data[Q1_lbl] so 

data[Q1_lbl] <> BLANK (), ALL(data[Q1_lbl] ) is equal to data[Q1_lbl] <> BLANK () for this case.

Q1 % N (W) =
CALCULATE ( SUM ( data[weight] ), KEEPFILTERS ( data[Q1_lbl] <> BLANK () ) )

 now just use KEEPFILTERS  in all measures and use DIVIDE instead of division operation.

Q1 % (W) = DIVIDE([Q1 % N (W)], [Q1 % B (W)])

[elaj]

Hi @AntrikshSharma ,

thanks for the fast reply.

keepfilters works fine for my mentions measure. this is great.

but for my base calculation i need the sum of all mentions.. so if i use keepfilters on them, they will be filtered... and once again i have 100% as a result. which is wrong.

but if i do it like my old approach i will have values in the first row:

so basically i dont want values in the first row.

and second:

when i now sort Q1_lbl after Q1_lbl_sort i have the other issue that my values go crazy:

how to solve this? why does the sorting destroy everything? is this a bug?

this is my desired result:

thanks for your answers 🙂

corrected_pbix 

[v-xuding-msft]

Hi @elaj ,

I have modified your formulas and put them into a new table "New Measures". For the blank rows, you could filter in filter pane. For more details, please download the pbix file to check.

 

And you could reference the blogs to learn more the function of FILTER.

Using the FILTER Function in DAX 

The FILTER Function in DAX 

Power BI Filter 

[elaj]

Hi,

yes i know i could filter the visual. the point was to not filter the visual. but to do it only inside the measure.

Is there no way to ignore the filter of the rows with "answer 1", "answer 2" etc. but NOT the blank row?

It seems like ALL (or another filter set inside a calculate statement) used on a certain column, always gives all values back in every case. it would be nice to have a way to configure WHERE! ALL should return all values. In my case... not in the blank row.

and the sort problem is still there.

thanks for your answers.

[AntrikshSharma]

This still doesn't works if you sort the column Q1_lbl by Q1_lbl_sort

[AntrikshSharma]

This is first time I have seen an issue because of the sort order. Now this is hurting even my head too!!