Set Filter Based on DAX Measure

[Kazoo60]

I am new to Power BI. I am working with a table of work items and need to return items which meet either of 2 conditions:

1) either the work item is scheduled for a week the user selects from a slicer

or

2) the  work item is late

I have created a disconnected slicer of week-ending due dates and can extract the selected value into a measure. That slicer is a dynamic table generating a value for the current week and 2 more weeks (dynamic based on the current date). It works fine.

I have created a calculated column in the table "AssignedTaskStatus" which indicates whether the task is "Late". This value is also returning accurately.

Now the problem:  I have written a DAX formula which I was hoping would evaluate for each row in the table and return "yes" or "no" for the row being included, and then I could set that column as a filter for the visual.

InSelectedWeekOrLate = 
VAR WkEndingDate = SELECTEDVALUE(CurrentWkEndingDate[WkEndingDate].[Date])
VAR Result = if(VALUES(TaskResourceAssignment[FinishWkEndingDate])=WkEndingDate, "yes"
            ,if(VALUES(TaskResourceAssignment[StartWkEndingDate])=WkEndingDate, "yes"
            ,if(VALUES(TaskResourceAssignment[AssignedTaskStatus])="Late", "yes","no")
            ))
return(Result)

I receive a calculation error:  A table of multiple values was supplied where a single value was expected.

The report layout appears below for context.  The slicer on the resource assigned to the work is connected to the table and works fine.  I would like only the tasks planned for the week of April 24th, and, late tasks to be displayed in the table.

I may not be pursuing a strategy that is feasible so please let me know if I'm looking at this all wrong and need to take another approach.

Thank you

[Anonymous]

HI @Kazoo60,

Current power bi does not support to create dynamic calculate column/table based on filter/slicers. Please use the measure formula instead. (measure, filter/slicer and calculate column work on different data levels, you can't use child level to interact with its parent)

Regards,

Xiaoxin Sheng

[Kazoo60]

Thank you @Anonymous  for the reply.  I am still a bit lost, however.  Can you give me a bit more explanation or an example of what you mean by "use measure formula instead"?  I believe I did create a measure formula (provided in my notes).  Or are there different types of measures?

Thank you

[Anonymous]

Hi @Kazoo60,

After I check on your DAX formula, I found you are using values function to extract current row content values from the table field. AFAIK, VALUES function can be used to extract aggerated row content that may contain a single value or list of values. (Power bi does not allow you to use math operators to compare list types of value with a single value)

You can try to use the following measure formulas if it suitable for your scenario:

InSelectedWeekOrLate =
VAR WkEndingDate =
    MAX ( CurrentWkEndingDate[WkEndingDate] )
VAR currStatus =
    SELECTEDVALUE ( TaskResourceAssignment[AssignedTaskStatus] )
VAR FinishWkEndingDate =
    VALUES ( TaskResourceAssignment[FinishWkEndingDate] )
VAR StartWkEndingDate =
    VALUES ( TaskResourceAssignment[StartWkEndingDate] )
RETURN
    IF (
        WkEndingDate IN UNION ( FinishWkEndingDate, StartWkEndingDate ),
        "yes",
        IF ( currStatus = "Late", "yes", "no" )
    )

For measure formulas, you can use them on the visual level filter to filter records:
Applying a measure filter in Power BI 

Regards,

Xiaoxin Sheng

[Kazoo60]

Thank you!   I have learned a lot about DAX syntax and strategy from the function you provided.  And yes - it does address the issue.

Tim