Forum Discussion

Julie_'s avatar
Julie_
Frequent Visitor
3 years ago
Solved

Filter a Distinctcount function by another measure

Hello,

 

i need a measure that calculated the distinct count of Items, but only for the months January and December. So it should just distinct count the items where the Measure i already wrote is not blank

The measure to sum the quantity of Jan&Dec 

 

VAR SumofQuantity_Jan_Dec =
IF(
    SELECTEDVALUE(v_DimCountrySoldTo[CountryEN]) = "Austria",
    CALCULATE(
        [GlobalDeliveredQuantity new],
        v_DimDate[MonthNumber] in {1,2,12}
    ),
    CALCULATE(
        [GlobalDeliveredQuantity new],
        v_DimDate[MonthNumber] in {1,12}
    )
)
 
Help please!
  • johnt75's avatar
    johnt75
    3 years ago
    Distinct count Jan Dec =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Column] ),
    FILTER ( VALUES ( 'Table'[Column] ), NOT ISBLANK ( [My Measure] ) )
)

    If column is enough to correctly identify the row and produce the value for the measure then this should work. If additional columns are needed in the filter context then you can replace VALUES with a SUMMARIZE statement including all necessary rows.

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    Try

    Distinct count Jan Dec =
IF (
    SELECTEDVALUE ( v_DimCountrySoldTo[CountryEN] ) = "Austria",
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Column] ),
        v_DimDate[MonthNumber] IN { 1, 2, 12 }
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Column] ),
        v_DimDate[MonthNumber] IN { 1, 12 }
    )
)
    • Julie_'s avatar
      Julie_
      Frequent Visitor
      3 years ago

      this doesn´t work because the item Table is not connected to the Date table, i need to filter it by the measure but i don´t know how

       

      • johnt75's avatar
        johnt75
        Super User
        3 years ago
        Distinct count Jan Dec =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Column] ),
    FILTER ( VALUES ( 'Table'[Column] ), NOT ISBLANK ( [My Measure] ) )
)

        If column is enough to correctly identify the row and produce the value for the measure then this should work. If additional columns are needed in the filter context then you can replace VALUES with a SUMMARIZE statement including all necessary rows.