Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
I need help writing a DAX code that will give me the values in the Quantity Required field
Basically, for every distinct Station_TruckNumber, I want to retrieve the Quantity of the Max CalendarDate
Solved! Go to Solution.
Hi @AbenaMina
Please confirm you have a date table. Are all other columns belong to the same table?
If you want to blank out the result of other rows then
Quantity Required =
VAR LastDate =
CALCULATE (
MAX ( TableName[CalendarDate] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
)
VAR LastDateValue =
CALCULATE (
MAX ( TableName[Quantity] ),
ALLEXCEPT (
TableName,
TableName[Station_TruckNumber] ),
TableName[CalendarDate] = LastDate
)
RETURN
IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )
Quantity Required =
VAR LastDate =
CALCULATE (
MAX ( TableName[CalendarDate] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
)
VAR LastDateValue =
CALCULATE (
MAX ( TableName[Quantity] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] ),
TableName[CalendarDate] = LastDate
)
RETURN
IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )
This one worked
No, it didn't work
It doesn't work for the year field. I want for the date field, select the Quantity for any given maximum date for every distinct station_trucknumber.
Take a look at the screenshot,
I am highlighting distinct station_trucknumber, and its corresponding max calendardate so from these 2 combinations, I want the corresponding Quantity
If you want to blank out the result of other rows then
Quantity Required =
VAR LastDate =
CALCULATE (
MAX ( TableName[CalendarDate] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
)
VAR LastDateValue =
CALCULATE (
MAX ( TableName[Quantity] ),
ALLEXCEPT (
TableName,
TableName[Station_TruckNumber] ),
TableName[CalendarDate] = LastDate
)
RETURN
IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )
This one work, I made a little modification for the bolded part : TableName[CalendarDate] = LastDate
Quantity Required =
VAR LastDate =
CALCULATE (
MAX ( TableName[CalendarDate] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
)
VAR LastDateValue =
CALCULATE (
MAX ( TableName[Quantity] ),
ALLEXCEPT (
TableName,
TableName[Station_TruckNumber],
TableName[CalendarDate]
)
)
RETURN
IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )
@AbenaMina
You are absolutely right. Also there is no need for calculate and ALLEXCEPT in 2nd variable
Quantity Required =
VAR LastDate =
CALCULATE (
MAX ( TableName[CalendarDate] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
)
VAR LastDateValue =
MAX ( TableName[Quantity] )
RETURN
IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )
I misunderstood the requirement. My mistake.
please try
Quantity Required =
VAR LastDate =
CALCULATE (
MAX ( TableName[CalendarDate] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
)
RETURN
CALCULATE (
MAX ( TableName[Quantity] ),
ALLEXCEPT (
TableName,
TableName[Station_TruckNumber],
TableName[CalendarDate] = LastDate
)
)
All the columns belong to the same table
You have first to create a CalrndarYear colum. New Column >
CalrndarYear = YEAR ( TableName[CalrndarDate] )
then New Measure >
Quantity Required =
CALCULATE (
MAX ( TableName[Quantity] ),
ALLEXCEPT ( TableName, TableName[Station_TruckNumber], TableName[CalendarYear] )
)
Hi @AbenaMina
Please confirm you have a date table. Are all other columns belong to the same table?
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |