Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a VERY simple formula:
Solved! Go to Solution.
If you just want to lookup the value for the max RefNum do you need to a max of the CurrentMonth column or could you just use the LookupValue function?
MaxMonth = LookupValue(,'Fact Table', 'Fact Table'[CurrentMonth]), max('Fact Table'[RefNum]))
Or if you have mulitple rows with the same RefNum you might need to do something like the following:
MaxMonth =
VAR _maxRefNum = max('Fact Table'[RefNum])
RETURN CALCULATE( MAX( 'Fact Table'[CurrentMonth]), 'Fact Table'[RefNum] = _maxRefNum )
Hi @Anonymous ,
You could display the returned result of below measure in a card visual.
MonNo for max refNo = VAR Curr_Date = TODAY () VAR Last_day_of_currMon = EOMONTH ( Curr_Date, 0 ) RETURN IF ( Curr_Date < Last_day_of_currMon, MONTH ( EOMONTH ( Curr_Date, -1 ) ), MONTH ( Last_day_of_currMon ) )
Best regards,
Yuliana Gu
Hi @Anonymous ,
You could display the returned result of below measure in a card visual.
MonNo for max refNo = VAR Curr_Date = TODAY () VAR Last_day_of_currMon = EOMONTH ( Curr_Date, 0 ) RETURN IF ( Curr_Date < Last_day_of_currMon, MONTH ( EOMONTH ( Curr_Date, -1 ) ), MONTH ( Last_day_of_currMon ) )
Best regards,
Yuliana Gu
The second parameter on your FILTER is not the expression you want, it should be(quote)
- 'A Boolean expression that is to be evaluated for each row of the table. For example, [Amount] > 0 or [Region] = "France" '
If you just want to lookup the value for the max RefNum do you need to a max of the CurrentMonth column or could you just use the LookupValue function?
MaxMonth = LookupValue(,'Fact Table', 'Fact Table'[CurrentMonth]), max('Fact Table'[RefNum]))
Or if you have mulitple rows with the same RefNum you might need to do something like the following:
MaxMonth =
VAR _maxRefNum = max('Fact Table'[RefNum])
RETURN CALCULATE( MAX( 'Fact Table'[CurrentMonth]), 'Fact Table'[RefNum] = _maxRefNum )
The lookupvalue function worked perfectly, THANK YOU! I'm still a little unsure as to why the calculate function doesn't work though...
@Anonymous wrote:The lookupvalue function worked perfectly, THANK YOU! I'm still a little unsure as to why the calculate function doesn't work though...
The problem with the original calculate is the call to FILTER(). Filter expects 2 parameters, a table to iterate over and a boolean expression, but your call to it looked like the following
filter('Fact Table', max('Fact Table'[RefNum]))
With a table and a numeric expression. What happens in this case is that the forumla engine attempts to do an implied cast on max('Fact Table'[RefNum]) to convert the number to a boolean value. And the way it does this is to treat values of 0 as false and non-zero as true. So this will effectively not filter any rows.
You should be able to fix this by comparing the current value of the [RefNum] with the max value by adding the following bold code.
MaxMonth = calculate(max('Fact Table'[CurrentMonth]), filter('Fact Table', 'Fact Table'[RefNum] = max('Fact Table'[RefNum])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |