topic Measure only at selected row in DAX Commands and Tips

Measure only at selected row

AudreyWen — Fri, 21 Feb 2020 08:37:21 GMT

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!! )

Re: Measure only at selected row

Anonymous — Fri, 21 Feb 2020 10:16:15 GMT

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.

Re: Measure only at selected row

Anonymous — Sat, 22 Feb 2020 15:19:43 GMT

First of all, you should tell us what the model is. Most importantly, what the relationships are between the tables. I can smell that the code you have authored is completely not the way it should be. Please show us the model first, then we'll tell you how to write the DAX correctly.

Re: Measure only at selected row

AudreyWen — Mon, 24 Feb 2020 15:00:50 GMT

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.

Re: Measure only at selected row

Anonymous — Mon, 24 Feb 2020 15:11:45 GMT

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 )

Re: Measure only at selected row

AudreyWen — Mon, 24 Feb 2020 15:46:36 GMT

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 🤔

Re: Measure only at selected row

Anonymous — Mon, 24 Feb 2020 15:49:11 GMT

What does it mean "it keeps on running again"?

Best
D

Re: Measure only at selected row

AudreyWen — Mon, 24 Feb 2020 15:53:57 GMT

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

Re: Measure only at selected row

Anonymous — Mon, 24 Feb 2020 15:56:56 GMT

I'm not sure if you know the difference between a measure and a calculated column... If we can't agree on the meaning of words, then I won't be able to help. Sorry.

Best
D

Re: Measure only at selected row

AudreyWen — Mon, 24 Feb 2020 16:03:28 GMT

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.

Re: Measure only at selected row

Anonymous — Mon, 24 Feb 2020 16:10:27 GMT

OK. So then, what's exactly wrong with the measure with my additions? Because we still don't know...

Best
D

Re: Measure only at selected row

AudreyWen — Tue, 25 Feb 2020 07:02:37 GMT

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.

Re: Measure only at selected row

Anonymous — Tue, 25 Feb 2020 08:52:17 GMT

Power BI is built on tabular databases that can easily handle tens of millions of rows. A measure can be slow if there is a lot of computation in it and the CPU is not powerful enough. Or it might be slow because it's written inefficiently. But if this is the case it should be optimized. For optimization techniques you should consult the book by The Italians - The Ultimate Guide to DAX.

Best
D

Re: Measure only at selected row

AudreyWen — Tue, 25 Feb 2020 14:21:25 GMT

Hi,

I will check it.

And many thanks for your replies and your time!

Re: Measure only at selected row

Anonymous — Tue, 25 Feb 2020 15:06:57 GMT

If you want to stay away from trouble, please NEVER expose fields in your fact tables to the user. Slice only through dimensions. If you decide to override this rule, you'll be in for a surprise rather sooner than later... and you'll not know why the numbers are what they are.

You can trust me on this one 🙂

Best
D