Filtering on a Card

Question

Hello,&nbsp;I am trying to create a filter on a card that shows the total number of territories that have a variance that is greater than zero.&nbsp; For example, suppose this is the list of territories and sales variances:&nbsp;Territory 1 &nbsp; &nbsp; &nbsp; $+1,000Territory 2 &nbsp; &nbsp; &nbsp; $+2,000Territory 3 &nbsp; &nbsp; &nbsp; $-1,500Territory 4 &nbsp; &nbsp; &nbsp; $-3,000Territory 5 &nbsp; &nbsp; &nbsp; $+4,000&nbsp;When I initially create the card and do a distinct count on the territories, it shows that I have 5 territories.&nbsp; I would like to add a filter based on the variance so that the total only includes territories that have a variance &gt;0 (in this case 3).&nbsp; When I add the filter to the card, the options are greyed out so that I cannot filter.&nbsp;Any ideas?&nbsp;ThanksGreg&nbsp;&nbsp;

vanessafvg · Answer

can you post a screen shot of what you are doing to filter it?&nbsp; also a screen shot of your data&nbsp; and how it is structured.

gregmorgan · Answer

Thanks for your quick response!&nbsp; Below is a screen show of the card and filters:&nbsp;&nbsp;&nbsp;The card above is based on a distinct count of territories.&nbsp; 24 is the total, but I want to only show the number of territories that have a variance that is greater than zero.&nbsp; But as you can see, the "$ Variance" filter is greyed out.&nbsp;My data (in a table view) looks as follows, the data in this table is aggregated based on detailed rows.&nbsp; (On the left are the territory names, I have cut off most of them).&nbsp; I want the care to only show the total territories that have a "$ Variance" &gt;0&nbsp;&nbsp;Let me know if you need additional information.&nbsp;ThanksGreg&nbsp;&nbsp;

gregmorgan · Answer

My first screen shot did not post for some reason, please see below:&nbsp;

vanessafvg · Answer

that doesn't look greyed out to me have you tried to drop it down?&nbsp;&nbsp;
&nbsp;
you could however manage this with a dax calculation
&nbsp;
Unique&nbsp;Territories&nbsp;with&nbsp;variance&nbsp;=VAR&nbsp;varonly&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;SUM&nbsp;(&nbsp;table[variance]&nbsp;)&nbsp;&gt;&nbsp;0,&nbsp;1,&nbsp;0&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DISTINCTCOUNT&nbsp;(&nbsp;table[territory]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;table,&nbsp;variances&nbsp;=&nbsp;1&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
&nbsp;

gregmorgan · Answer

Thanks. It is definitely greyed out (hard to see in the screen shot). For some reason, Power BI will not allow a filtering a value on a distinct count of another column. (I have seen other users comment onthis as well).I tried your solution and set up a new measure as follows:Unique Territories with variance =VAR varonly =&nbsp;&nbsp; IF ( SUM ( 'SalesbyTerritory'[Variance] ) &gt; 0, 1, 0 )RETURN&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DISTINCTCOUNT ( 'SalesbyTerritory'[Territory] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( SalesbyTerritory, SalesbyTerritory[Variance] = 1 )&nbsp;&nbsp; )When I add this to my card, I just get '(Blank)'. I am sure I am doing something wrong, are you able to determine the issue based on the code above?&nbsp;Thanks again!

Forum Discussion

Filtering on a Card

9 Replies

Related content

MULTIPLE CARD

Ranking KPI card

Filter a card using a table

Card Visual

Where is the Multi-row card