DAX

[kalavala]

How to write the formula Power bi in DAX

Sum(Case When (columnname) < 10 Then 1 Else 0 End) Over (Partition By ColumnName)

[Eric_Zhang]

---

@kalavala wrote:

How to write the formula Power bi in DAX

Sum(Case When (columnname) < 10 Then 1 Else 0 End) Over (Partition By ColumnName)

---

@kalavala

Besides @Reid_Havens's solution, or a more intuitive approach.

Measure =
SUMX ( Table1, SWITCH ( TRUE (), Table1[Column1] < 10, 1, 0 ) )

[Reid_Havens]

Hi there,

Happy to help!

So it looks like you're wanting a count of rows when [ColumnName] is less than 10 correct? It also seems you're using the OVER partition to group by the [ColumnName] values for the count? If that is the case then a DAX Measure could look like:

=
CALCULATE (
    COUNTA ( [ColumnName] ),
    [ColumnName] < 10,
    FILTER ( TableName, [ColumnName] = EARLIER ( [ColumnName] ) )
)

Hope this helps!

Reid Havens - Owner

Havens Consulting Inc.

[kalavala]

Hello  Havens,

 the below calcuation part columnname 1 

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

please help me 

=
CALCULATE (
    COUNTA ( [ColumnName] ),
    [ColumnName] < 10,
    FILTER ( TableName, [ColumnName1] = EARLIER ( [ColumnName] ) )
)

[Reid_Havens · Accepted solution]

Hi there,

So the EARLIER function is grouping by values in a specified column, so those column names need to be the same, in the example below you're using ColumnName1 against ColumnName, those should match and then the query should work.