Forum Discussion

Shree_185's avatar
Shree_185
Helper I
3 years ago
Solved

DAX Calculated Column Help

Hi Everyone,    Trying to replicate the below view in one line query (calculated column)   Calculate column = Region-wise Distinct Count ID  WHERE Date is not blank OR Date is not equal to 01/01/...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    If you need the visual you posted, it should work. If you need it in a table visual, you need to add an ALLEXCEPT function in the filter expression:

    Count ID ALLEXCEPT =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Region] ),
        NOT ( ISBLANK ( 'Table'[Date] ) )
            && 'Table'[Date] <> DATE ( 2000, 1, 1 )
    )
)

    I've attached the sample PBIX file

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

Try:

Count ID =
CALCULATE(DISTINCTCOUNT(Table[ID), FILTER(Table, NOT(ISBLANK(Table[Date])) && Table[Date] <> DATE(2000, 1, 1)))

Shree_185's avatar
Shree_185
Helper I
3 years ago

PaulDBrown Thanks for your help. The logic didn't work? or maybe I did some mistake. can you please send me the PBIX file if possible?

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    If you need the visual you posted, it should work. If you need it in a table visual, you need to add an ALLEXCEPT function in the filter expression:

    Count ID ALLEXCEPT =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Region] ),
        NOT ( ISBLANK ( 'Table'[Date] ) )
            && 'Table'[Date] <> DATE ( 2000, 1, 1 )
    )
)

    I've attached the sample PBIX file