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...
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
Icon for Super User rankSuper 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