DAX FILTER on the results of SUMMARIZECOLUMN

[mr_fnord]

I am trying to find the number of days with "heavy rain", defined as hourly rainfall > 0.5 inches, per month. My rainfall records are hourly, and it is possible that there are several hours of heavy rain in a day, but I need to count the day only once. SUMMARIZE and MAX() seem like a simple way to solve this, but I cannot find the syntax that lets me FILTER on the generated column, "DlyMax".

DaysPerMonth = COUNTROWS(FILTER(SUMMARIZECOLUMNS([Date], "DlyMax", MAX(TimeSeries[Precip])) , DlyMax > 0.5))

I cannot find any syntax that will allow me to reference the result of the calculation in the SUMMARIZE. Any suggestions?

[parry2k · Accepted solution]

@mr_fnord try this measure

DaysPerMonth = 
SUMX (
   VALUES ( TimeSeries[Date] ), 
   IF ( CALCULATE ( MAX(TimeSeries[Precip] ) ) > 0.5, 1 )
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mr_fnord]

This works, thanks! I did not realize that VALUES and SUMX could be used to get distinct values and an aggregate grouped by those values.

[aj1973]

Hi @mr_fnord 

Using SUMMARIZE as a variable inside COUNTROWS it is like using a Virtual table, Have you tried to create that Table inside your model and see the Outcome? You might be missing something and that's why you are getting the filter or the column that you are looking for.