Forum Discussion

bzeeblitz's avatar
bzeeblitz
Helper IV
1 year ago
Solved

Measure notactioned

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.

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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's avatar
    bhanu_gautam
    1 year ago

    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],
    ", "
    )
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      bzeeblitz
      Helper IV
      1 year ago

      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's avatar
        bhanu_gautam
        Super User
        1 year ago

        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],
        ", "
        )
        )