Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 🙂

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Anonymous
Not applicable

@Anonymous  -

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

View solution in original post

22 REPLIES 22
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

You sir are awesome! Thank you

You are welcome.  Thank you for your kind words.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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?

Hi,

Try this measure

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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? 

Anonymous
Not applicable

@Anonymous  -

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

Anonymous
Not applicable

Aha, i see. Thank you for the response - is there any workaround for this?

Anonymous
Not applicable

@Anonymous  - What are the desired results?

Anonymous
Not applicable

So in regards to what you explained to me very brilliantly, i believe it would make more sense for the "reader" of the dashboard - if the sum of the line chart correlates with the KPI. This meaning no dublicates that you mentioned could happen 🙂

Anonymous
Not applicable

@Anonymous - So you would only want the App to appear in 2017 or 2018, even though it actually applies for both? Which year would you want it to be included, and which year excluded?

Anonymous
Not applicable

So i actually do have a year/month filter in the report - but i guess it is not possible to do without making the filter static? Or am i misunderstanding?

Anonymous
Not applicable

@Anonymous - I don't understand - The question is: what do you want the value to be for 2017, 2018, 2019, separately, and summed up? Do you want the card to find the value for each of the 3 years, and then add them together?

Anonymous
Not applicable

@Anonymous 

Hi Natel,

When i use the slicer (filter) in my report and choose year 2017 i want to get the total sum of 2017 as the KPI but in the line graph i want them to be distributed by month. So when adding all the months together that should match the total sum KPI number. If i filter on 2018 the same story with the total sum in the kpi for 2018, and the total sum distributed by month of 2018. Same story for 2019.

I hope this is better to understand 😉 If it is possible to work out it would be much appreciated.

Thank you

Anonymous
Not applicable

@Anonymous - You could add the following additional measure, and compare the results between the two. The new measure specifically adds up the values from the months. :

Current Measure:

 

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

New Measure:

 

Above5 Sum Months = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS(
            SUMMARIZE ( Tabel; [AppId]; 'working dates'[RelativeMonth] ); 
            "ABCD"; [UniqueSessionsCount]
        );
        [ABCD] > 5
    )
)

 

 

Hope this helps,

Nathan

Anonymous
Not applicable

@Anonymous  Hi Natel - so i understand the issue i am facing - but i still have not been able to solve the problem. The linegraph still shows if a user has been online both months with more than 5 sessions. Is there any way where the app id (in this case) is only counted once even though i filter on multiple months and/or years?

Anonymous
Not applicable

@Anonymous  - I'm not sure what exactly you're looking for. Previously, you stated that you wanted the line graph to add up to the Card. Could you describe exactly what you want the measure to do? Specifically, if there are 2 different months with 5+, what would you want to happen?

 

Anonymous
Not applicable

Hi @Anonymous . Thank you for responding!

As an example i have included a picture:PowerBIDoubleCount.PNG

As you can see when i choose a year and a period (multiple month selection) the sum of the line graph is higher than the KPI. This is due to the reason you explained. I would like the linegraph to only count a unique app id once in the period i filter on.

Do you think this is possible and/or do you have an idea how to do this differently?

Anonymous
Not applicable

@Anonymous - I suppose it's possible - would you want the earliest month with +5 to contain the user, or the last month, or month with the most usage? 

I'm concerned that this will not provide an intuitive result.

Also, why are there 2 different line graphs? It appears they're both showing the same information.

 

Cheers,

Nathan

Anonymous
Not applicable

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

Thanks,

Nathan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.