HASONEFILTER not working - suggestions?

[kressb]

I have four tables:

Table 1 - Sites, Groups (Site 1, Site 2 = Group A, etc), connected to Table 2 & Table 3

Table 2 - Maximum # for Site by day

Table 3 - Total # done for Site by day

Table 4 - Date Dimension, connected to Table 2 & Table 3

The data tables are by Site. I'm making a summary table by Group that counts # of days at max and over max.

I have the table completed, but I can't get the Total Line to reflect correctly for At Max/Over Max.

For Over Max, I am using:

If(HASONEFILTER(Table1[Group]),
  SumX(DateDimension,
                                     Calculate(IF(
                                                        SUM(Table3[Total])-Sum(Table2[Max])>0,
                                                        Count(DateDimension[Date]))),
Sumx(Filter(Table1,SUM(Table3[Total])-Sum(Table2[Max])>0),
          Count(DateDimension[Date])))

Instead of the 2 in yellow above, I just get a blank.. anyone willing to throw out some ideas as to why?

[kressb]

@Greg_Deckler I believe I got this HASONEFILTER formula from you, any idea why it wouldn't work to count dates?

[Greg_Deckler · Accepted solution]

@kressb Fairly certain that should be HASONEVALUE.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

At Max: =

SUMX (

VALUES ( Table1[Group] ),

CALCULATE ( IF ( SUM ( Table2[Max] ) = SUM ( Table3[Total] ), 1, 0 ) )

)

Over Max: =

SUMX (

VALUES ( Table1[Group] ),

CALCULATE ( IF ( SUM ( Table2[Max] ) < SUM ( Table3[Total] ), 1, 0 ) )

)

[kressb]

@Jihwan_Kim thanks for responding.

This is by individual day - every single day of the year - so that formula won't work.
The formula i have does work, for everything except for returning a total.

any other ideas?