Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I use a measure in a report and i want this measure only to use the rows with a specific selected value.
A row has a status in column "Status". for example: "Delivered" or "Open" or "In Delivery" or "Blocked" or "Cancelled".
I want my measure only use the rows with the status "Open" or "In Delivery".
Beneath is my measure:
StockLevel =
var currentArticle = if (HASONEVALUE(PBIOrderOverzicht[Artikel NR.]); min(PBIOrderOverzicht[Artikel NR.]);0)
var StockLevel = CALCULATE(sum(AvStock[TotalStock]); AvStock[Artikelnr.] = currentArticle)
var x =
CALCULATE(
SUM(PBIOrderOverzicht[Aantal bevestigd]);
PBIOrderOverzicht[Artikel NR.] = currentArticle;
FILTER(
ALL(PBIOrderOverzicht);
PBIOrderOverzicht[Bevestigde datum]< MAX(PBIOrderOverzicht[Bevestigde datum])
)
)
var result = StockLevel - x
return result
Can someone help me with this?
Many thanks!
(the measure is created with help of "JustJan" a member here. Thanks!! )
Hi Darlove,
Sorry for my late reply but i can only test at work.
I have rebuild the model so that it is easier to show.
Now my measure is:
StockLevel =
var currentArticle = if (HASONEVALUE(AvStokLevel[Artikelnr]); min(AvStokLevel[Artikelnr]);0)
var StockLevel = CALCULATE(sum(AvStock[TotalStock]); AvStock[Artikelnr.] = currentArticle)
var x =
CALCULATE(
SUM(AvStokLevel[Aantal bevestigd]);
AvStokLevel[Artikelnr] = currentArticle;
FILTER(
ALL(AvStokLevel);
AvStokLevel[Datum bevestigd]< MAX(AvStokLevel[Datum bevestigd])
)
)
var result = StockLevel - x
return result
At this moment this measure keeps on running cause it calculate with every row in a table. I only need the calculation on selected rows.
(i don't know why but i can't insert picturs so i have to do it like this)
I have 2 tables:
Tabel: AvStock |
Artikelnr |
MATNR |
TotalStock |
Tabel: AvStockLevel |
VBELS |
POSNT |
Artikelnr |
Aantal bevestigd |
Datum bevestigd |
Status Levering |
The relation between those 2 is Artikelnr.
Column "Status Levering" has the following options:
Status Levering: |
Geleverd |
Geannuleerd |
NVT |
Geblokkeerd |
In levering |
Open |
In a report i want all the columns from tabel AVStockLevel and Column TotalStock from table AvStock. And then a column with my measure "StockLevel" that only calculate on selected rows based on Status Levering. In this report I only wan't the rows with Status Levering: "Open" or "In levering" or "Geblokkeerd" or "Niet bevestigd".
VBELN | POSNR | Artikelnr | Status Levering | Aantal bevestigd | Datum bevestigd | TotalStock | StockLevel |
376146 | 10 | 93000 | Open | 41 | 4-3-2020 | 109 | 109 |
376075 | 10 | 93008 | Open | 1 | 12-3-2020 | 45 | 45 |
376146 | 20 | 93020 | Open | 79 | 4-3-2020 | 233 | 233 |
374833 | 10 | 93020 | Open | 3 | 12-3-2020 | 233 | 154 |
375753 | 10 | 93020 | Open | 1 | 12-3-2020 | 233 | 151 |
60027913 | 10 | 1019886 | Open | 270 | 31-12-2020 | 258 | 258 |
371736 | 10 | 1020408 | In levering | 30 | 27-2-2020 | 120 | 120 |
374792 | 10 | 1020410 | Open | 10 | 12-3-2020 | 17 | 17 |
375695 | 10 | 1020415 | In levering | 1 | 26-2-2020 | 22 | 22 |
374792 | 20 | 1020669 | Open | 10 | 12-3-2020 | 14 | 14 |
371736 | 20 | 1020680 | In levering | 33 | 27-2-2020 | 70 | 70 |
373165 | 10 | 1020680 | Open | 30 | 6-3-2020 | 70 | 37 |
373838 | 10 | 1024012 | In levering | 150 | 26-2-2020 | 1420 | 1420 |
375383 | 10 | 1024014 | Open | 2 | 5-3-2020 | 94 | 94 |
376106 | 40 | 1024014 | Open | 2 | 12-3-2020 | 94 | 92 |
374649 | 20 | 1024027 | In levering | 100 | 26-2-2020 | 187 | 187 |
375983 | 10 | 1024066 | Open | 20 | 12-3-2020 | 0 | 0 |
373874 | 10 | 1026003 | Open | 20 | 26-2-2020 | 37 | 37 |
368270 | 10 | 1026004 | In levering | 135 | 26-2-2020 | 210 | 210 |
371364 | 50 | 1026005 | Open | 89 | 26-2-2020 | 86 | 86 |
376149 | 50 | 1026006 | Open | 6 | 4-3-2020 | 149 | 149 |
374146 | 20 | 1026008 | In levering | 10 | 26-2-2020 | 89 | 89 |
374670 | 10 | 1026065 | Open | 50 | 11-3-2020 | 153 | 153 |
374803 | 10 | 1030004 | Open | 10 | 4-3-2020 | 0 | 0 |
376546 | 10 | 1030065 | Geblokkeerd | 0 | 26-2-2020 | 2074 | 2074 |
372638 | 10 | 1033003 | Open | 2000 | 25-2-2020 | 382 | 382 |
376337 | 90 | 1033633 | Open | 2 | 4-3-2020 | 0 | 0 |
375315 | 50 | 1033640 | Open | 1 | 26-2-2020 | 12 | 12 |
376628 | 10 | 1033640 | In levering | 2 | 26-2-2020 | 12 | 11 |
375428 | 390 | 1033646 | Open | 5 | 3-3-2020 | 294 | 294 |
368311 | 130 | 1033656 | Open | 6 | 26-2-2020 | 0 | 0 |
375428 | 350 | 1033662 | Open | 2 | 3-3-2020 | 97 | 97 |
376625 | 50 | 1033662 | Open | 16 | 12-3-2020 | 97 | 95 |
375315 | 100 | 1033665 | In levering | 50 | 26-2-2020 | 1737 | 1737 |
375428 | 420 | 1033665 | Open | 45 | 3-3-2020 | 1737 | 1687 |
375699 | 60 | 1033666 | In levering | 50 | 28-2-2020 | 2323 | 2323 |
375428 | 430 | 1033666 | Open | 40 | 3-3-2020 | 2323 | 2273 |
My measure workt perfectly in a test table with rows that only contents the wanted status. But not on the total tabel that i have.
My explanation is a bit of a mess but i still hope that you can help.
StockLevel =
...
...
...
// added just before 'return result'
var __statusLevering = SELECTEDVALUE(AvStockLevel[Status Levering])
var __shouldDisplay =
__statusLevering in {
"open", "in levering",
"geblokkeerd", "niet bevestigd"
}
return
// instead of returning result...
if( __shouldDisplay, result )
Hi!
I have changed my measure to;
StockNiveau =
var currentArticle = if (HASONEVALUE(AvStokLevel[Artikelnr]); min(AvStokLevel[Artikelnr]);0)
var StockLevel = CALCULATE(sum(AvStock[TotalStock]); AvStock[Artikelnr.] = currentArticle)
var x =
CALCULATE(
SUM(AvStokLevel[Aantal bevestigd]);
AvStokLevel[Artikelnr] = currentArticle;
FILTER(
ALL(AvStokLevel);
AvStokLevel[Datum bevestigd]< MAX(AvStokLevel[Datum bevestigd])
)
)
var result = StockLevel - x
var __statusLevering = SELECTEDVALUE(AvStokLevel[Status levering])
var __shouldDisplay =
__statusLevering in {
"Open"; "In levering";
"Geblokkeerd"; "Niet bevestigd"
}
return
if( __shouldDisplay; result )
Only now it keeps on running again 🤔
Oh Sorry, I mean that when i add this measure in my report (in the table) than it won't give the measure as column. It looks like it keeps on thinking what it should do
Hi,
I know the difference. I make a new measure in the report on table AvStockLevel. There i put the measure. Than i drag the measure and put it in the table that i have in my report.
Hi,
I don't know. Maybe nothing is wrong with it, but maybe there are to many rows (60.565) in the dataset and that will only be more everytime.
In my first measure it was the same problem it kept on loading when i put the measure in my report. Then I have made a new dataset with an Excelfile with only the rows with the status i need (741 rows at the moment, sometimes it will be more or less), then the measure works perfectly in my report. Only now I have to update my model, export the rows to Excel everytime and than i must update the model again. This will be to confusing if my colleagues have to work with this later on. I'm hoping to find a way that it will work at ones.
Hi,
I will check it.
And many thanks for your replies and your time!
Hi @AudreyWen
i assume [StockLevel] measure is working as fine as per your requirement.
Create new measure as below for new requirement.
Measure=calculate([StockLevel],filter(PBIOrderOverzicht,PBIOrderOverzicht[Status] in{"Open","Delivery"}))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |