Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 🙂
Solved! Go to Solution.
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.
@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
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.
You sir are awesome! Thank you
You are welcome. Thank you for your kind words.
Sorry for writing this additional question.
With the measure that you posted:
Hi,
Try this measure
Between 1 and 5 = COUNTROWS(FILTER(SUMMARIZE(VALUES(Tabel[AppId]);[AppId];"ABCD";[UniqueSessionsCount]);[ABCD]>=1&&[ABCD]<=5))
Hope this helps.
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
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 -
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
Aha, i see. Thank you for the response - is there any workaround for this?
@Anonymous - What are the desired results?
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 - 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?
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 - 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
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 - 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 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 - 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?
Hi @Anonymous . Thank you for responding!
As an example i have included a picture:
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 - 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 - Can you share your measure(s)? Or your pbix?
Thanks,
Nathan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |