Reply
Regular Visitor
Posts: 28
Registered: ‎04-04-2018

Boolean calculations and filters

I've seen boolean calculations using filters like this: 

trues_count = CALCULATE( COUNT([Element A]),
    FILTER(Table, [Element A] = "True"))

Since I'm using more filters I need more control on those counts, which could resemble otther languages that treat TRUE as 1 and FALSE as 0. 
For example,

A5_proportion = CALCULATE( AVERAGE(Table[indicator A] <= 5), 
    Table[Filter B], 
    TREATAS( VALUES(Calendar[date] ), Table[event_date])
)


This isn't possible because AVERAGE requires a column.  
Does anyone know a good way to achieve this?  Thanks. 

Super User
Posts: 627
Registered: ‎02-29-2016

Re: Boolean calculations and filters

[ Edited ]

@Diego-mx

If you want to average an expression evaluated over each row of a table, you can use the iterator AVERAGEX.

Also, in DAX, booleans are not automatically cast as numbers, but you can multiply by 1 or add 0 to achieve the same effect.

 

An expression which I think achieves what you want, in place of AVERAGE(Table[indicator A] <= 5), is

AVERAGEX ( Table, ( Table[indicator A] <= 5 ) + 0 )

Regards,

Owen

Regular Visitor
Posts: 28
Registered: ‎04-04-2018

Re: Boolean calculations and filters

 

Thanks for this hint.  
I shall mark this as a solution. 

Still, I'm missing how to combine regular filters with virtual ones from TREATAS. 

I'll post a new message. 

Cheers.