Forum Discussion

s_fs's avatar
s_fs
Frequent Visitor
1 year ago

Distinct Count based on Condition

I have created a table visual there I have a column which is numeric and it is weekyear like 202501,202502,202503 ... Then I have a another columns called in-hand goods and demand  for the respective...
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

s_fs First create a measure

Measure to count the distinct weekyears you can cover:

dax
Covered Weeks Count =
VAR CurrentWeek = MAX('Table'[WeekYear])
VAR InHandGoods = MAX('Table'[InHandGoods])
VAR DemandTable =
FILTER(
'Table',
'Table'[WeekYear] > CurrentWeek
)
VAR CumulativeDemand =
SUMX(
DemandTable,
CALCULATE(
SUM('Table'[Demand]),
FILTER(
'Table',
'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
)
)
)
VAR CoveredWeeks =
COUNTROWS(
FILTER(
DemandTable,
CumulativeDemand <= InHandGoods
)
)
RETURN
CoveredWeeks

 

Measure to calculate the sum of the demand for the weeks you can cover:

dax
Covered Demand Sum =
VAR CurrentWeek = MAX('Table'[WeekYear])
VAR InHandGoods = MAX('Table'[InHandGoods])
VAR DemandTable =
FILTER(
'Table',
'Table'[WeekYear] > CurrentWeek
)
VAR CumulativeDemand =
SUMX(
DemandTable,
CALCULATE(
SUM('Table'[Demand]),
FILTER(
'Table',
'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
)
)
)
VAR CoveredDemand =
SUMX(
FILTER(
DemandTable,
CumulativeDemand <= InHandGoods
),
'Table'[Demand]
)
RETURN
CoveredDemand

s_fs's avatar
s_fs
Frequent Visitor
1 year ago

Hi bhanu_gautam , 

Thanks for the query, my in hand goods and demand are measures. I believe MAX won't work for measures right??? 

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    s_fs , use this it reference measure directly

    dax
    Covered Weeks Count =
    VAR CurrentWeek = MAX('Table'[WeekYear])
    VAR InHandGoods = [InHandGoods] -- Reference the measure directly
    VAR DemandTable =
    FILTER(
    'Table',
    'Table'[WeekYear] > CurrentWeek
    )
    VAR CumulativeDemand =
    SUMX(
    DemandTable,
    CALCULATE(
    [Demand], -- Reference the measure directly
    FILTER(
    'Table',
    'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
    )
    )
    )
    VAR CoveredWeeks =
    COUNTROWS(
    FILTER(
    DemandTable,
    CumulativeDemand <= InHandGoods
    )
    )
    RETURN
    CoveredWeeks

     

    dax
    Covered Demand Sum =
    VAR CurrentWeek = MAX('Table'[WeekYear])
    VAR InHandGoods = [InHandGoods] -- Reference the measure directly
    VAR DemandTable =
    FILTER(
    'Table',
    'Table'[WeekYear] > CurrentWeek
    )
    VAR CumulativeDemand =
    SUMX(
    DemandTable,
    CALCULATE(
    [Demand], -- Reference the measure directly
    FILTER(
    'Table',
    'Table'[WeekYear] <= EARLIER('Table'[WeekYear])
    )
    )
    )
    VAR CoveredDemand =
    SUMX(
    FILTER(
    DemandTable,
    CumulativeDemand <= InHandGoods
    ),
    [Demand] -- Reference the measure directly
    )
    RETURN
    CoveredDemand