topic Re: Filter a Distinctcount function by another measure in DAX Commands and Tips

Filter a Distinctcount function by another measure

Julie_ — Mon, 24 Apr 2023 08:59:12 GMT

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!

Re: Filter a Distinctcount function by another measure

johnt75 — Mon, 24 Apr 2023 09:26:07 GMT

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 } ) )

Re: Filter a Distinctcount function by another measure

Julie_ — Mon, 24 Apr 2023 09:34:24 GMT

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

Re: Filter a Distinctcount function by another measure

johnt75 — Mon, 24 Apr 2023 09:48:13 GMT

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.