Ok,
so this is a bit of a weird calculation.
Issue:
I have some intensive calculations (cumulative Sales by attribute)and when a user opens up the report page with the calculations.
Proposed solution:
I want to find a way that if a user lands on the page and the data selection returns a large set of data then I want to ->
short circuit the difficult calculation to return blank (Cumulative Sales by Attribute returns BLANK if rowcount exceeds the limit otherwise it calculkates the true value)
Make visible a message saying āData set size exceeds x number of rows please filtert he reportā
CountRows(ALLSELECTED(FactTable) could give me what I want but I notice
the warning message works as the contect is the page and so returns the correct calc.
When this is used withing the chart the number of rows that are in ALLSELECTED changes to a lower number.
I am looking for a way to calculate the number of rows in the fact table that are selected when you land on a report page and for this to be available even if it is displayed inside a chart that has further filters.
Iām guessing Iām missing something, as on the surface this looks not too difficult as you note and based on my first read. On that basis, a collection of branching measures should get you where you want, such as:
Sales in Context Row Count = COUNTROWS( Sales )
Target Row Count = 20
Too Many Rows Message = "Data set size exceeds " & [Target Row Count] & " rows; please filter the report"
Row Count Message =
IF( [Sales in Context Row Count] > [Target Row Count], [Too Many Rows Message], "Number of rows OK" )
Your final comment seems to ask that there be additional filters on a visual that further reduce this row count, but youād still like to be able to reference this row count, so I remain unsure if this will be useful to you or not; in any event, here it is. If this does not help with your issue, please provide a mock-up of exactly what youāre looking for so that it can be explored further.
Thats close to the code that I have but it does not work.
There are Three contexts on the page.
(Assuming the limit is 1M rows)
Page - here it is simple if the number of rows is say 3.5M then we show the warning
Chart - as there are further filters the number of rows in this context is 0.9M Rows and shows the calculation
Matrix - This has les filters and has 1.8M rows so no colculation.
What I am trying to achieve is to consistantly show message and short circuit the calculation or Not at the page level rather than the individual itens.
I thought it was too easy ā¦ unfortunately I still canāt see the issue ā¦ can you please provide marked-up screenshots and/or a mock-up of the end result youāre looking for? Greg
Thanks for posting your question @ells. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
I have added a sample report.
On Page 1 Filtered - the poage itself if you place a card that shows 10,889 for the CountRows(ALLSELECTED(Sales))
If you look at the same measure in the table it shows 7616 as the table is filtered by TopN.
My hearts desire is to see the Measure RC at Page level show (the number of rows at the page level.
I have hard coded RC at Page Level to show what I am expecting and as you can see as we are at a different context the result is different.
I think using ALL or calculate to remove the context may not work as potentially there may be a page filter applied.
(I added a blank check to not show rows for which there are no sales.)
The [Total Row Count] measure always returns the total count of 10,899 even if you apply a visual filter of Top N (8) filter to the matrix and a page filter of Sales[Price] < 1000.
Hi @ells , did the response provided by @Greg and @Despo help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
@Despo
Thanks. Countrows will not cut the mustard.
Countrows will return the count of the rows in the data based on the context. I need the count at Page context. Not visual context or at report context. This is why I am struggling.
On the page āPage 1 Filteredā I wish to display in a card and inside the visual the number of items in the fact table at the page contect level.
As can be seen in the report uploaded, The rowcount changes context.
Uploaded a second report that hopefully shows it a luittle clearer.
EPage Row Count.pbix (455.1 KB)
I see you already have these measures in your sample PBIX, so Iām guessing thereās more to your issue. If so, please describe it as fully as you can.
@Greg,
I must be doing something wrong then.
When I put the measure RC Page Level in the visual then the number retruned is just the rows at the current context. i.e Visual level
Inside the visual that should show 4909 which is the context at Page Level.
I the example provided
All rows = 10889 (Page 1)
on Page 1 Filtered (my report page)
Number of rows at Page level = 4909 (There is a page filter)
Number of Rows in the visual are 3338 (There is a filter on the visual)
I can get to the 10889 - ALL(Sales)
I can get the number of rows in the Visual(3338)
I can not get to 4909. !
The reason this is a pain in the but is I have a complex calculation to go into the visual and I wnat to short circuit the calc if the number of rows on the page are greater than a set number.
Everything is in place bar the nuber of rows at Page level.
This matters because I need to consistently show a messagfe saying page selection > x please filter the page for the calculations to show.
Once I have raised the message I dont want to do that calculation in all of the visuals on the page.
Everything absolutely everything is in place except for the calc to get number of rows at page level.
so it is like I need something called PAGESELECTED
You should still be able to get what you want by referencing the [RC Page Level] measure, just donāt include it inside the visual or the visual evaluation context will be applied, once again returning your 3338 number instead of the 4909 youāre looking for.
Greg
But then how do I control the vissual to not do the calculation?
The required solutions is to not calculate some of the metrics in the visual if the number of rows on the page exceeds a set limit.
So the calculation in the visual geow to zero if Row Count for the poage exceeds a set limit. The reasons its page is different visuals on the page using the same calc want to either be shown or not.
This could even become a standard for all pages in the report. If the number of rows at page level > x then show message
We currently have the ALL Selected measure and are using it and it has been rejected so trying to find a way of getting this at the page value and alsmost storing it / fixing it there.
Hi @ells. if you keep the calculation outside the visual then all is good. I added a measure to set the threshold and check the threshold outside the visual, then used the check inside the visual.
Calculation Threshold = 1500
Do Calculation or not =
SWITCH( TRUE(),
ISBLANK( [RC Page Level]), "No",
[RC Page Level] > [Calculation Threshold], "No",
"Yes"
)
Use the [Do Calculation or not] measure in your visual, try selecting 2018, then 2019, then 2020 in the filter pane, and see if thatās what youāre looking for.
I know it will work outside the visual. Getting the message to work is easy. Getting the page count inside the visual to short ciircuit the calculation does not.
Always this is about a consistant Row count for the page regardless of the current context. That way when I put up a warning saying the calculation wont be displayed , it really wont, and also not calculated so the visual is fast.
Got a feeling I am going to have to settle for
Cant do that
Iām not clear on why just comparing to the threshold outside the visual wonāt work for you ā¦ Iām sure we can come up with something, itās just a matter of iterating the issue description and draft solutions until you find it; Power BI never ceases to surprise me in whatās possible.
If youād like to continue, please make a further example PBIX showing exactly what you currently have both below and above the threshold, along with Excel mock-ups of exactly what youād like to have happen both below and above the threshold and we can pursue.