topic Newbie with a problem understanding code. in DAX Commands and Tips

Newbie with a problem understanding code.

peraleonardo — Fri, 24 May 2024 06:59:42 GMT

Hello everyone, I have been selected for an internship with a large company, and in this role, I am reviewing and studying the Power BI project that the corporate office has sent to our location.

The project is based on the study of corporate KPIs. In the first few days, everything went smoothly, but now I am revisiting the measures, especially the CALCULATE function. I have found that many newbies have trouble immediately understanding the CALCULATE function and everything related to it.

The code in question that I can't understand is this:

xx = Calculate (

Sum('table'[y]),

FILTER

( ALL ( 'table'[date]),

'table'[date] = MAX('table'[date]) && 'table'[date] <= TODAY()

))

How does this code return the maximum date for each month, year, and quarter?

Awaiting your help and kind response, I wish you good work.

Re: Newbie with a problem understanding code.

mark_endicott — Fri, 24 May 2024 08:48:32 GMT

peraleonardo - This code will return the MAX date because it is used in conjunction with ALL().

When ALL() is used it removes any existing filters over a table or column. CALCULATE then triggers Context Transition to re-apply the filter specified in the measure as a new filter.

Here are three great articles to learn some more, but also please feel free to ask me more questions.

https://www.sqlbi.com/articles/introducing-calculate-in-dax/

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

If this has helped you enough, please accept it as the solution.

Re: Newbie with a problem understanding code.

xifeng_L — Fri, 24 May 2024 08:58:28 GMT

The MAX function in the code will automatically calculate the maximum date outside the corresponding date range.

xx = Calculate (

Sum('table'[y]),

FILTER

( ALL ( 'table'[date]),

'table'[date] = MAX('table'[date]) && 'table'[date] <= TODAY()

))

Not quite sure if I misunderstood your question.

Re: Newbie with a problem understanding code.

Anonymous — Mon, 27 May 2024 06:26:08 GMT

Thanks solutions from xifeng_L and mark_endicott

Hi, peraleonardo

ALL ( 'table'[date])

Here the filter is used to apply the condition to all rows in the Table. The all function removes any filters that may be applied to the Date column of the Table, ensuring that all dates are taken into account in the calculation.

'table'[date] = MAX('table'[date]) && 'table'[date] <= TODAY()

This condition in the FILTER is to find dates in the Table that are both the largest date (the most recent date in the data) and less than or equal to today's date.
You can click on the drop-down link to learn the basics of the DAX:
Learn DAX basics in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Newbie with a problem understanding code.

peraleonardo — Mon, 27 May 2024 07:00:14 GMT

Hi,

Understood but I also have another consideration. When I try to modify the second condition and insert:

'table'[date] <= MAX(DATE(yyyy,mm,dd)) I always get in the matrix the maximum date like if the filter apply by the matrix itself it's not working.

how can you help me out with this?

Re: Newbie with a problem understanding code.

Anonymous — Mon, 27 May 2024 08:05:21 GMT

Hi, peraleonardo

Thank you very much for your reply. This is true if you intend to take all dates into account in your calculations without taking into account any filters applied elsewhere in the report.

ALL ( 'table'[date])

However, if you wish to keep some filters, you may need to adjust this part of the formula. You should try using ALLEXCEPT to keep the other filters in your matrix.

ALLEXCEPT function (DAX) - DAX | Microsoft Learn

KEEPFILTERS function (DAX) - DAX | Microsoft Learn

It would be helpful if you could make some virtual data and describe the output that you are looking forward to, it would help me understand your problem.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.