cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JacobPBI Regular Visitor
Regular Visitor

How to filter on a card - works in table view

Hi experts,

I think this is a very basic problem. I hope you can help me.

I have been able to filter and get the number i want (31027) in the "table" vizualisation (bottom of the picture).

The filter does not work in a card (seen on the top of the picture)

The filter i need on the card is the distinct count of all the app id's who has more than 5 sessions.

See the picture below:

CardNotFiltering.png


I am almost certain, that it has something to do with the distinctcount measure not being able to filter the way i want. I have tried to calculate with a filter, but it is not working out for me.

Any ideas?

Thank you very much Smiley Happy

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: How to filter on a card - works in table view

Hi

Try this measure and drag it to your visuals

=COUNTROWS(FILTER(SUMMARIZE(VALUES('qs v_FactUsage[AppId]),[AppId],"ABCD",[Uniquesessions's]),[ABCD]>5)

Hope this helps.

Super User
Super User

Re: How to filter on a card - works in table view

@JacobPBI  -

The problem is that "Distinct Counts" are semi-additive.

The Line Chart applies the calculation for each year, e.g. answers the question for 2018:

In 2018, how many products had a session count greater than 5?

The sum of the Line Chart could potentially be less than the Card if an App had 7 total sessions, but 3 were in 2017 and 4 in 2018 - in that case, the App, which is counted in the Card, would not be counted at all.

In other cases, the sum of the Line Chart could count the same App twice, e.g. it had more than 5 sessions in 2017 and more than 5 in 2018.

Hope this helps,

Nathan

22 REPLIES 22
Super User
Super User

Re: How to filter on a card - works in table view

@JacobPBI  - Can you share your measure(s)? Or your pbix?

Thanks,

Nathan

JacobPBI Regular Visitor
Regular Visitor

Re: How to filter on a card - works in table view

Hi Natel,

Thanks for replying.

The only measures i have are:

DISTINCTCOUNT (AppID) called UniqueAppis's and DISTINCTCOUNT (SessID) called UniqueSessions.

I have included a picture of how my data structure and filters on the table - if that clarifies anything.
datastructureandfilters.PNG
Please tell me if you need anything else Smiley Happy

Super User
Super User

Re: How to filter on a card - works in table view

@JacobPBI  - Can you show the same information for the Card visual?

Super User
Super User

Re: How to filter on a card - works in table view

Hi

Try this measure and drag it to your visuals

=COUNTROWS(FILTER(SUMMARIZE(VALUES('qs v_FactUsage[AppId]),[AppId],"ABCD",[Uniquesessions's]),[ABCD]>5)

Hope this helps.

Highlighted
JacobPBI Regular Visitor
Regular Visitor

Re: How to filter on a card - works in table view

You sir are awesome! Thank you

Super User
Super User

Re: How to filter on a card - works in table view

You are welcome.  Thank you for your kind words.

JacobPBI Regular Visitor
Regular Visitor

Re: How to filter on a card - works in table view

Sorry for writing this additional question.

With the measure that you posted:


Above5 = COUNTROWS(FILTER(SUMMARIZE(VALUES(Tabel[AppId]);[AppId];"ABCD";[UniqueSessionsCount]);[ABCD]>5))

If i want to filter in the range between 1 and 5 (not just above 5) - do i need to make an "if" statement or use "or"?

What would you suggest and how would it look?
Super User
Super User

Re: How to filter on a card - works in table view

Hi,

Try this measure

Between 1 and 5 = COUNTROWS(FILTER(SUMMARIZE(VALUES(Tabel[AppId]);[AppId];"ABCD";[UniqueSessionsCount]);[ABCD]>=1&&[ABCD]<=5))

Hope this helps.

JacobPBI Regular Visitor
Regular Visitor

Re: How to filter on a card - works in table view

Hi again, and thank you for the help! 

In regards to the measure i accepted as a solution - it is working well but i seem to have a problem when i am putting it in a graph. The numbers do not add up - i have included a picture SumInGraphIssue.png

This is the exact same measures just put in a graph over 3 years. When i filter on the specific years it shows me the number in the graph. But when i add them all together there is a difference from the KPI on top. Do you have any idea why this is? 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 125 members 1,341 guests
Please welcome our newest community members: