Forum Discussion

AudreyWen's avatar
AudreyWen
Icon for Helper I rankHelper I
6 years ago

Measure only at selected row

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

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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.
    • AudreyWen's avatar
      AudreyWen
      Icon for Helper I rankHelper I
      6 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        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 )