Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago
Solved

Firstnonblank in virtual table

Hi,
I'm stacked with using Firstnonblank function suing it for virtual table. Here is the virtual table code:

AR MAct =
UNION(
   ROW("MA",[Sold Prod Qnty],"RowNum",1),
   ROW("MA",[-1M],"RowNum",2),
   ROW("MA",[-2M],"RowNum",3),
    ROW("MA",[-3M],"RowNum",4),
    ROW("MA",[-4M],"RowNum",5),
    ROW("MA",[-5M],"RowNum",6),
    ROW("MA",[-6M],"RowNum",7),
    ROW("MA",[-7M],"RowNum",8),
    ROW("MA",[-8M],"RowNum",9),
    ROW("MA",[-9M],"RowNum",10),
    ROW("MA",[-10M],"RowNum",11),
    ROW("MA",[-11M],"RowNum",12),
    ROW("MA",[-12M],"RowNum",13),
    ROW("MA",[-13M],"RowNum",14),
    ROW("MA",[-14M],"RowNum",15),
    ROW("MA",[-15M],"RowNum",16),
    ROW("MA",[-16M],"RowNum",17),
    ROW("MA",[-17M],"RowNum",18),
    ROW("MA",[-18M],"RowNum",19),
    ROW("MA",[-19M],"RowNum",20),
    ROW("MA",[-20M],"RowNum",21),
    ROW("MA",[-21M],"RowNum",22),
    ROW("MA",[-22M],"RowNum",23),
    ROW("MA",[-23M],"RowNum",24)
  )

The result I want is to get the relevant "RowNum" of the corresponding first non balnk record from the virtual column "MA".
For some resonons Firstnonblank() function does not work - I can not assign it to the Mact[MA], nor Mact[RowNum] coumn in variable.

Please help.

Thanks in advance
 

 

  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Hi George1973 

    Are the [-1M], [-2M] etc measures applying a date shift of a given number of months to the [Sold Prodn Qty] measure (or similar)?

    I suspect there may be a more efficient way of handling this calculation, depending on what you want to do with RowNum. For example, you could apply FIRSTNONBLANK to months of a Date table, rather than referencing 24 different measures.

     

    Putting that to one side, to answer your original question, I would not suggest using the FIRSTNONBLANK function to find the first RowNum with nonblank MA. FIRSTNONBLANK is normally applied to a column of a physical table (which has lineage), and returns a 1-row 1-column table, retaining lineage of the original column. It is often used when you want to apply the resulting value as a filter in further calculations.

     

    In this case, you could more simply write:

    VAR LookupTable =
UNION (
    ROW( "MA", [Sold Prod Qnty], "RowNum", 1 ),
    ROW( "MA", [-1M],"RowNum", 2 ),
    ROW( "MA", [-2M],"RowNum", 3 ),
   //...
    ROW( "MA", [-22M],"RowNum", 23 ),
    ROW( "MA", [-23M],"RowNum", 24 )
  )

VAR FirstNonBlankRowNum =
    MINX ( LookupTable, IF ( NOT ISBLANK ( [MA] ), [RowNum] ) )

    Regards,

    Owen

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    Hi George1973 

    Are the [-1M], [-2M] etc measures applying a date shift of a given number of months to the [Sold Prodn Qty] measure (or similar)?

    I suspect there may be a more efficient way of handling this calculation, depending on what you want to do with RowNum. For example, you could apply FIRSTNONBLANK to months of a Date table, rather than referencing 24 different measures.

     

    Putting that to one side, to answer your original question, I would not suggest using the FIRSTNONBLANK function to find the first RowNum with nonblank MA. FIRSTNONBLANK is normally applied to a column of a physical table (which has lineage), and returns a 1-row 1-column table, retaining lineage of the original column. It is often used when you want to apply the resulting value as a filter in further calculations.

     

    In this case, you could more simply write:

    VAR LookupTable =
UNION (
    ROW( "MA", [Sold Prod Qnty], "RowNum", 1 ),
    ROW( "MA", [-1M],"RowNum", 2 ),
    ROW( "MA", [-2M],"RowNum", 3 ),
   //...
    ROW( "MA", [-22M],"RowNum", 23 ),
    ROW( "MA", [-23M],"RowNum", 24 )
  )

VAR FirstNonBlankRowNum =
    MINX ( LookupTable, IF ( NOT ISBLANK ( [MA] ), [RowNum] ) )

    Regards,

    Owen

    • George1973's avatar
      George1973
      Icon for Helper V rankHelper V
      4 years ago

      Dear Owen,
      Thanks for the sugegstion.
      Actualy you've inspiried me to write much better better formula by reffering to the original data table. Here is the code:

      1st Month Sales Activity = 
VAR MnIndex=SELECTEDVALUE(DateKey[MonthIndex])
VAR FirstAct= calculate(FIRSTNONBLANK(DateKey[MonthIndex],[Sold Prod Qnty]),
DATESINPERIOD (
      DateKey[Date],        
      MAX ( DateKey[Date]),
      -24,                   
      MONTH                  
    )
)

Return
MnIndex-FirstAct+1


      Where "MonthIndex" in the data table is additioanl calculated column giving to each month an unique, incremental number.
      The main challange for me is that Months in my projects are dinamic - Depending on the selected month in the slacer, all other -24 months are beeing changed.

      Thanks for the hints!!!