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,
I am running visuliaztion that queries off of a DirectQuery and some imported tables in my PowerBi file. I can break down the parameters and run them fine by doing it based off of a category instead of all together. The total results of 3 lines items coming back cumulatively. I am guessing when I run all the categories together I exceed the 1,000,000 rows, but doing the categories serpate it hits less than that. I am just hoping to understand this better to try and figure out a way of troubleshooting the issue. .
Below is my visual and with my filters selected. My end result is to show me what item are out of stock and for how long during a date range for 4 locations. Right now I have just 1 day selected and the 4 locations, along with just one buyer's items. This total comes to 958 internalIDs, or count of unquie products and locations. I am having a hard time trying to understand why this would have a error of the 1,000,000 rows. Any idea? Am I missing something big, that is obvious?
Thank you,
Noel
Solved! Go to Solution.
I ended up doing the following and so far it is working, unless you can see anything that it could miss.
OutofStock = if(
'ns vwInventorySnapshot'[DetereminedQuantity] = 0
&& 'ns vwInventorySnapshot'[Date] >= 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]
&& or('ns vwInventorySnapshot'[Date]<= 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate],'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] = BLANK()),1,0)
Hi, @NBOnecall
Based on your description, the limit can occur in cases where Power BI is not fully optimizing the queries sent, and there is some intermediate result being requested that exceeds the limit. It can also occur whilst building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.
You may refer to the following links.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @NBOnecall ,
maybe your measure creates a value for every possible combination.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
@mwegener So it is definitely my measure, as when I removed it data was populated. How can I make it more efficient to run only on the filtered items?
CountDaysOOS2.1 = CALCULATE(
COUNTROWS('ns vwInventorySnapshot'),
REMOVEFILTERS(Dimtime[Date]),
FILTER(
'ns vwInventorySnapshot',
'ns vwInventorySnapshot'[DetereminedQuantity] = 0
&& 'ns vwInventorySnapshot'[Date] >= if(isblank(MAX(MIN('ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]),MIN(Dimtime[Date]))),TODAY(),MAX(MIN('ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]),MIN(Dimtime[Date])))
&& 'ns vwInventorySnapshot'[Date] <=if(isblank(MIN(MAX('ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate]),MAX(Dimtime[Date]))),TODAY(),MIN(MAX('ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate]),MAX(Dimtime[Date])))
)
)
Which columns do you show in the visual?
Currently trying to show InternalId, LocationId, LocationName, Average of SalesAmount, Average of Rolling 30 Days, and CountDaysOOS2.1 (measure).
Hi @NBOnecall ,
it's really hard, because i have no sample data to test.
But try this.
CountDaysOOS2.1 =
CALCULATE (
COUNTROWS ( 'ns vwInventorySnapshot' ),
REMOVEFILTERS ( Dimtime[Date] ),
FILTER (
FILTER (
'ns vwInventorySnapshot',
'ns vwInventorySnapshot'[DetereminedQuantity] = 0
),
'ns vwInventorySnapshot'[Date]
>= IF (
ISBLANK (
MAX (
MIN ( 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock] ),
MIN ( Dimtime[Date] )
)
),
TODAY (),
MAX (
MIN ( 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock] ),
MIN ( Dimtime[Date] )
)
)
&& 'ns vwInventorySnapshot'[Date]
<= IF (
ISBLANK (
MIN (
MAX ( 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] ),
MAX ( Dimtime[Date] )
)
),
TODAY (),
MIN (
MAX ( 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] ),
MAX ( Dimtime[Date] )
)
)
)
)
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
I ended up doing the following and so far it is working, unless you can see anything that it could miss.
OutofStock = if(
'ns vwInventorySnapshot'[DetereminedQuantity] = 0
&& 'ns vwInventorySnapshot'[Date] >= 'ns vwInventorySnapshot'[ns vwFirstDateInStockByLocation.FirstDateInStock]
&& or('ns vwInventorySnapshot'[Date]<= 'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate],'ns vwInventorySnapshot'[ns ValogixPlanning.PhaseOutDate] = BLANK()),1,0)
Hi,
Could you please explain in detail the formula that you used and how do you used it?
In which table do you used it? Is a measure or a calculated column?
The columns that you used are part of the table or did you create it?
I making that questions in order to understand how to apply that to my case.
Thank you
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |