Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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
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.
@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
Thank you so much. It worked 🙂
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |