Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count and filter numeric values only

Hi guys, 

 

Im crushing my head with following issue in PBI.

I have data in column DTM which contains numeric and text values. 

I need to count of DTM items which are in range 0 to 10, their Status is Open and its numeric value only (DTM column)

Results would look like - Count: 2

Any idea how DAX formula should look like?

 

 

Thanks

 

 

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    You can create a measure like

    Num Open =
COUNTROWS (
    FILTER (
        'Table',
        VAR numVal =
            IFERROR ( VALUE ( 'Table'[DTM] ), BLANK () )
        RETURN
            numVal >= 0
                && numVal <= 10
                && 'Table'[Status] = "Open"
    )
)

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You can create a measure like

    Num Open =
COUNTROWS (
    FILTER (
        'Table',
        VAR numVal =
            IFERROR ( VALUE ( 'Table'[DTM] ), BLANK () )
        RETURN
            numVal >= 0
                && numVal <= 10
                && 'Table'[Status] = "Open"
    )
)