Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

@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
Employee
Employee

@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.

Thank you so much. It worked 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.