cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ynew
Helper I
Helper I

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.

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@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])
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Ynew
Helper I
Helper I

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.

v-yuezhe-msft
Microsoft
Microsoft

@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])
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thank you so much. It worked 🙂

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors