Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi.
First time I'm not able to solve my issue with this forum 😞
To summarize, I have a table with 3 columns : Date, Value, Modified
I can have multiple values for the same date.
I have already a measure to get the max value related to Date matched to an external calendar :
calculate(MAX('Table'[Value]), Filter ('Table', 'Table'[Date] >= calculate(min('Calendar'[Date])) && 'Table'[Date] <= calculate(max('Calendar'[Date]))
This works fine, but I need to make chang to get the most recent Value (based on Modified column).
I spent hours without success trying to add something in filter like
&& 'Table'[Modified]=MAX('Table'[Modified])
but it seems not taken ina ccount my calendar in this case 😞
Thanks for your help or advices....
Solved! Go to Solution.
Hi, @Alex_MM
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
lastt =
VAR MAXM =
CALCULATE ( MAX ( 'Table'[Modified] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Date] >= MIN ( 'Calendar'[Date] )
&& 'Table'[Date] <= MAX ( 'Calendar'[Date] )
&& 'Table'[Modified] = MAXM
)
)
3.Here's my final result, which I hope meets your requirements.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
When uploading a file, please be careful to delete sensitive information.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That works great in your sample aligned with my need ...
Still some issue to implement in my whole formula a bit more complicated
Small question : why keeping the
MAX ( 'Table'[Value] ),
as goal is to have only one value ??
Thanks for your time 👍
Hi, @Alex_MM
Thank you for your prompt reply; I’m pleased to hear of your interest in this area.
Firstly, my understanding is that I retained the CALCULATE(MAX('Table'[Value])) because I believe this is part of your requirement. However, if that’s not the case, I would recommend replacing it with the SUM() function. If the content in the FILTER() remains unchanged, whether you use SUM(), MAX(), or MIN() in a measure, they will return the current row value. The difference is primarily in the total section, which explains why your previous attempts to add new filter conditions were ineffective.
This is related to how these measures depend on context, and SUM() has stricter requirements: it necessitates that the data type is numeric.
For further details, please refer to:
SUM function (DAX) - DAX | Microsoft Learn
MIN function (DAX) - DAX | Microsoft Learn
MAX function (DAX) - DAX | Microsoft Learn
CALCULATE function (DAX) - DAX | Microsoft Learn
Secondly, why do I emphasise that the content in FILTER() must remain unchanged? This is because you can apply the ALL() or ALLSELECTED() functions within it, which will yield different results.
For further details, please refer to:
ALL function (DAX) - DAX | Microsoft Learn
ALLSELECTED function (DAX) - DAX | Microsoft Learn
Next, there are calculated columns and calculated tables, which, like measures, are constructed using DAX language. However, the latter is more akin to physical tables, so you can view them in the table view.
For further details, please refer to:
Measures in Power BI Desktop - Power BI | Microsoft Learn
Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn
Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn
Here are some posts related to similar issues that I hope will be helpful to you. I hope my explanation aids your understanding of the principles behind measures.
Solved: Re: How to setup a visual for a table with only a ... - Microsoft Fabric Community
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Alex_MM
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
lastt =
VAR MAXM =
CALCULATE ( MAX ( 'Table'[Modified] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Date] >= MIN ( 'Calendar'[Date] )
&& 'Table'[Date] <= MAX ( 'Calendar'[Date] )
&& 'Table'[Modified] = MAXM
)
)
3.Here's my final result, which I hope meets your requirements.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
When uploading a file, please be careful to delete sensitive information.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |