Forum Discussion

Ynew's avatar
Ynew
Icon for Helper I rankHelper I
8 years ago
Solved

Referencing a measure which returns a table with single cell inside another measure

Hello,

 

I want to use a measure (which includes LASTNONBLANCK function) in another mesure as below:

 

I have three columns with hundreds of rows.

Column 1: Number of orders

Column 2: Date and time

Column 3: Month

Columns are sorted based on the month name.

I want to have a meaure(measure A) to know what is the last month name, then sum over all orders with month name equal to result of mesure A.

 

So, I tought that I can fnd the last month by LASTNONBLANK function, then use a sum function with a filter (measure B).

 

 

A= LASTNONBLANK('Table1'[Month], 1)

B= COUNTAX(FILTER('Table 1',[Month]=[A]),[Number of orders])

 

But I don't get the result I want. B counts all orders without considering the filter. However, A works correctly, but not correctly insode measure B.

 

I think the problem is with the result of measure A. As I know LASTNONBLANK function returns a table with a sinlge row and column. So it is not a text. And I need to use a text in measure B like :COUNTAX(FILTER('Table 1',[Month]="Sep"),[Number of orders]).

 

I cannot find a way to change the vaue format of measure A to return a sinlge text value.

 

Do you have any suggestions?

 

Thnk you.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Ynew,

    Please create the measures using DAX below.

    A = CALCULATE(LASTNONBLANK(Table1[Month],1),ALL(Table1))
    B = COUNTAX(FILTER(Table1,Table1[Month]=[A]),Table1[Number of orders])


    Regards,
    Lydia

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Ynew,

    Please create the measures using DAX below.

    A = CALCULATE(LASTNONBLANK(Table1[Month],1),ALL(Table1))
    B = COUNTAX(FILTER(Table1,Table1[Month]=[A]),Table1[Number of orders])


    Regards,
    Lydia

    • Ynew's avatar
      Ynew
      Icon for Helper I rankHelper I
      8 years ago

      Thank you so much. It worked :)

  • Ynew's avatar
    Ynew
    Icon for Helper I rankHelper I
    8 years ago

    Hi,

     

    I had already asked a question as in my post for LASTNONLANK function. However, not I see that this is only working if "Month"column is sorted A-Z!

     

    What if "Month"column in Table1 is like:

     

    Table1:

    Month

    Jan

    Feb

    Mar

    Apr

    May

    Jun

     

    So this way Month column is sorted based on the dates. And I want to select the "Jun"as the last month.

     

    I used to solve this problem by adding a column with nuber in "Table1" in it as below:

    Month#    Month    

    1               Jan

    2               Feb

    3               Mar

    4               Apr

    5               May

    6               Jun

     

    And my other table named "Report1"

     

    Report1:

    Volume    Month    

    4               Jan

    3              Jan

    2               Feb

    7               Feb

    9               Feb

    3               Mar

    7               Apr

    5               Apr

    1               May

    8               Jun

     

    I use a measure to find the last month as:

     

    LastMonth= VLOOKUP ('Table1'[Month], 'Table 1'[Month#], MAX('Table1'[Month#]))

    TotalVolume= COUNTAX(FILTER('Report1', 'Report1'[Month]=[LastMonth]),'Report1'[Volume])

     

    Measure "LastMonth" works fine itself. However, it is not working when referred in the second measure. 

    I searched and see that VLOOKUP function returns a single value but do not know why its value is not working as a text value in another measure. 

     

    I appreciate your help.