Measure notactioned

[bzeeblitz]

I have list1 and list2

List1 has Item number,Date fields

List2 has item number,Date,status

1.

How to create measure to get itemnumber based on the Date from list1

2.how to get list1 itemnumber which status is not actioned since we don't have status field in list1.

[bhanu_gautam · Accepted solution]

@bzeeblitz Make sure that list1 and list2 are related by the item number field. You can create a relationship in the "Model" view in Power BI.

DAX
ItemNumberMeasure =
VAR SelectedDate = SELECTEDVALUE(list1[Date])
RETURN
CALCULATE(
FIRSTNONBLANK(list2[Item number], 1),
list2[Date] = SelectedDate
)

[bhanu_gautam · Accepted solution]

@bzeeblitz Make sure that list1 and list2 are related by the item number field. You can create a relationship in the "Model" view in Power BI.

DAX
ItemNumberMeasure =
VAR SelectedDate = SELECTEDVALUE(list1[Date])
RETURN
CALCULATE(
FIRSTNONBLANK(list2[Item number], 1),
list2[Date] = SelectedDate
)

[bzeeblitz]

I'm getting error fetching Data for the visual calculation error in measure.dax comparison operations do not support comparing values of type Date with values of type text.consider using the value or format function to convert one of the values

[bhanu_gautam · Accepted solution]

@bzeeblitz Check data type in both of them Table

ItemNumberMeasure =
VAR SelectedDate = SELECTEDVALUE(list1[Date])
RETURN
CALCULATE(
FIRSTNONBLANK(list2[Item number], 1),
list2[Date] = VALUE(SelectedDate)
)

Additionally, to get the item numbers from list1 where the status in list2 is not "actioned", you can create a measure like this:

NotActionedItemNumbers =
CALCULATE(
CONCATENATEX(
FILTER(
list1,
RELATED(list2[Status]) <> "actioned"
),
list1[Item number],
", "
)
)