Forum Discussion

Kazoo60's avatar
Kazoo60
Frequent Visitor
6 years ago

Set Filter Based on DAX Measure

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

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Kazoo60
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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