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

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