Forum Discussion

Shree_185's avatar
Shree_185
Icon for Helper I rankHelper I
4 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
    4 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
Icon for Community Champion rankCommunity Champion
4 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
    Icon for Helper I rankHelper I
    4 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
      Icon for Community Champion rankCommunity Champion
      4 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