Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AudreyWen
Helper I
Helper I

Measure only at selected row

‎02-21-2020 12:37 AM

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

Message 1 of 15
5,716 Views
0
14 REPLIES 14
Anonymous
Not applicable

‎02-22-2020 07:19 AM
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.
Message 3 of 15
5,643 Views
0
AudreyWen
Helper I
Helper I
In response to Anonymous

‎02-24-2020 07:00 AM

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".

 

VBELNPOSNRArtikelnrStatus LeveringAantal bevestigdDatum bevestigdTotalStockStockLevel
3761461093000Open414-3-2020109109
3760751093008Open112-3-20204545
3761462093020Open794-3-2020233233
3748331093020Open312-3-2020233154
3757531093020Open112-3-2020233151
60027913101019886Open27031-12-2020258258
371736101020408In levering3027-2-2020120120
374792101020410Open1012-3-20201717
375695101020415In levering126-2-20202222
374792201020669Open1012-3-20201414
371736201020680In levering3327-2-20207070
373165101020680Open306-3-20207037
373838101024012In levering15026-2-202014201420
375383101024014Open25-3-20209494
376106401024014Open212-3-20209492
374649201024027In levering10026-2-2020187187
375983101024066Open2012-3-202000
373874101026003Open2026-2-20203737
368270101026004In levering13526-2-2020210210
371364501026005Open8926-2-20208686
376149501026006Open64-3-2020149149
374146201026008In levering1026-2-20208989
374670101026065Open5011-3-2020153153
374803101030004Open104-3-202000
376546101030065Geblokkeerd026-2-202020742074
372638101033003Open200025-2-2020382382
376337901033633Open24-3-202000
375315501033640Open126-2-20201212
376628101033640In levering226-2-20201211
3754283901033646Open53-3-2020294294
3683111301033656Open626-2-202000
3754283501033662Open23-3-20209797
376625501033662Open1612-3-20209795
3753151001033665In levering5026-2-202017371737
3754284201033665Open453-3-202017371687
375699601033666In levering5028-2-202023232323
3754284301033666Open403-3-202023232273

 

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.

 

Message 4 of 15
5,611 Views
0
Anonymous
Not applicable
In response to AudreyWen

‎02-24-2020 07:11 AM 
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 )
Message 5 of 15
5,608 Views
0
AudreyWen
Helper I
Helper I
In response to Anonymous

‎02-24-2020 07:46 AM

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 🤔 

Message 6 of 15
5,555 Views
0
Anonymous
Not applicable
In response to AudreyWen

‎02-24-2020 07:49 AM
What does it mean "it keeps on running again"?

Best
D
Message 7 of 15
5,552 Views
0
AudreyWen
Helper I
Helper I
In response to Anonymous

‎02-24-2020 07:53 AM

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 

Message 8 of 15
5,551 Views
0
Anonymous
Not applicable
In response to AudreyWen

‎02-24-2020 07:56 AM
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
Message 9 of 15
5,549 Views
0
AudreyWen
Helper I
Helper I
In response to Anonymous

‎02-24-2020 08:03 AM

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. 

Message 10 of 15
5,548 Views
0
Anonymous
Not applicable
In response to AudreyWen

‎02-24-2020 08:10 AM
OK. So then, what's exactly wrong with the measure with my additions? Because we still don't know...

Best
D
Message 11 of 15
5,326 Views
0
AudreyWen
Helper I
Helper I
In response to Anonymous

‎02-24-2020 11:02 PM

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.

 

Message 12 of 15
5,321 Views
0
Anonymous
Not applicable
In response to AudreyWen

‎02-25-2020 12:52 AM
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
Message 13 of 15
5,316 Views
0
AudreyWen
Helper I
Helper I
In response to Anonymous

‎02-25-2020 06:21 AM

Hi,

 

I will check it.

 

And many thanks for your replies and your time!

Message 14 of 15
5,307 Views
0
Anonymous
Not applicable
In response to AudreyWen

‎02-25-2020 07:06 AM
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
Message 15 of 15
5,303 Views
0
Anonymous
Not applicable

‎02-21-2020 02:16 AM

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.

Message 2 of 15
5,673 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All