Card showing wrong rowcount. whatif filter ignored

[jakubk]

my data source is star schema datamart

i have a simple viz that's filtered by slicers based on dimension attribute values

i also have a whatif param that i'm using to filter the viz to only records where my physical measure is less than or equal to the whatif value

i've set it up by doing a calc in my fact table:= measure - whatif value

then i added this as a filter to the viz and set it as less than or equal to 0. I'm not sure if there's a less convoluted way of setting this up?

I want to add a card or something that shows a simple # of rows that make up the viz but the number i'm getting is too big. I've verified it by changing my viz to a table; i can see it's 20 rows but the row count card is showing 200+

it seems like the card is ignoring the whatif filter and i cant seem to add it to the card as a filter and configure it

is this a bug?

is there a workaround?

[parry2k]

@jakubk reason it works in table because your filter is calculated for each row, whereas on card visual there is no row context and it is calculated at total level. you should use countx function to calculate # of rows 

[jakubk]

Thanks for the tip but i'm going to need more info.

Use it how?

I've looked up the docs for countx

I dont want to hardcode filters in a dax query

surely i can get a rowcount of a filtered dataset without having to duplicate the filters/slicers and make sure i keep my dax up to date?

[Icey]

Hi @jakubk ,

You can build a concrete example and tell me the result you want. I will conduct specific tests.

Best Regards,

Icey

[jakubk]

posted a long reply but forum 'an unexpected error'ed me. argh

workbook here: https://drive.google.com/file/d/1zvrZIrNxgBO20yFNm-C_hDFn7tOinVBm/view?usp=sharing

It's a dataset with 5 rows

I just want a label that shows how many rows are in the filtered dataset

i know it's the whatif param that's causing the problem. The way i had to write it seems very unintuitive and convoluted but i couldn't get it to work by writing it 'logically' with a =If(quantity<=what if param,TRUE,FALSE) calc

powerbi doesn't let me add it as a page filter, i'm guessing because it's a measure? But when i create it as an attribute it would always return true

powerbi doesn't let me add the measure filter as a filter to the label card

Thanks

[parry2k]

@jakubk try changing you measure to this

Measure = [Quantity Filter Value]-sum(Data[Quantity])

[jakubk]

ok... it made no difference?

the card is still showing 2 as the rowcount

but there's only 1 row in the resultset

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jakubk]

so i need to explicitly filter the value that's shown in the card/label?

Row Count = countrows(filter(Data,Data[Quantity]<='Quantity Filter'[Quantity Filter Value]))

There's no way to make the what if parameter filter 'global'/page level?

[Ashish_Mathur]

Hi,

I am not sure of your requirement.  Did my solution not work for you?

[PaulDBrown]

@jakubk 

See if this works for you:

 You can use the "Quantity filter" in slicers, and all the "filter pane" filter options.

For example:

Here is the file if you're interested. PBIX file 

[jakubk]

Hi Paul

The filtered table isn't working because it's showing rows where qty is > the selected quantity filter

as a user i want to see the records that are below a threshold that I set via a slider

I also want to see a label display the number of records

@Ashish_Mathurno your solution deleted my table altogether and put in some weird between slider. I wasn't sure what question you were answering

I resolved this a week or so ago but it doesn't feel like a good solution. I was hoping i was doing it the wrong way and there was a more logical/cleaner way

what I ended up with is here: https://drive.google.com/file/d/1iU1xKHl2dfrQb2oCTSTpxagYtyPL-hxF/view?usp=sharing

[PaulDBrown]

@jakubk 

I can see why you think it isn't working...but it is. I actually left the [Sum Quantity] measure in the table, which will display all values relevant to the filter context (but is unaffected by the "what if" condition), on purpose so that you could see what was going on. If you remove this measure, you will get the table showing only the rows which meet the "Quantity Filter" condition ( <= ).

For example:

The relevant measures for your purposes are therefore: 

[Row count] which counts the rows where the condition is met

[Quantity less than or equal to] which returns the quantity only for rows which meet the condition.

You also mention you want a label which displays the number of records. That is what the Card visual in green in my example is doing. Or are you after something else?

[jakubk]

ok, so its still very fiddly with having to create not obvious custom filtered measures

isn't there a way to create a boolean flag and use that as filter?

eg

Column = if(Data[Quantity]<=SELECTEDVALUE('Quantity Filter'[Quantity Filter]), true, false)

i tried but it always returns false

[PaulDBrown]

@jakubk 

It won't work. Calculated columns are not dynamic. They are calculated when the table is loaded and are immune to slicers and filters

[jakubk]

ahhh

And I can't add a boolean as a measure and filter on it because if this bug (already tried)  - https://community.powerbi.com/t5/Desktop/BOOLEAN-column-can-t-apply-filter-in-Power-BI-Desktop/td-p/...

thanks for the info