cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FrankWoody
Helper I
Helper I

How to use a measure as a filter

There we go again.
Good afternoon !!

I created a table with the following measurements:

Value.id is direct text from the table.
StartDatetime = MAX(Add1[Startdate time])
Duedate = MAX(add1[value.dueDateTime])
Max CompleteDatetime = MAX(add1[value.completedDateTime])
Progress =

VAR C = IF(
[Date_Deadline]> TODAY() && [Max CompleteDatetime] = 0,"In progress",IF([Max CompleteDatetime]> 0, "Done" ,"Overdue")
                  )
return C

The problem is how to filter a measure? like Progress to Filter:
in progress
Done
overdue
duadateschanges V3.png

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

One of ways is to create a calculated column. However, if you are not tempted to create a calcuated column, then try creating a separate table like below (Profress table). And then, write a measure like below.

Please check the below picture and the attached pbix file.

 

Untitled.png

 

Progress measure: = 
VAR _IDtablewithprogressmeasure =
    IF (
        HASONEVALUE ( Data[ValueID] ),
        SWITCH (
            TRUE (),
            MAX ( Data[Duedate] ) > TODAY ()
                && MAX ( Data[Completedate] ) = 0, "In progress",
            MAX ( Data[Completedate] ) > 0, "Done",
            "Overdue"
        )
    )
RETURN
    IF (
        _IDtablewithprogressmeasure IN VALUES ( 'Progress table'[Progress] ),
        _IDtablewithprogressmeasure
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

One of ways is to create a calculated column. However, if you are not tempted to create a calcuated column, then try creating a separate table like below (Profress table). And then, write a measure like below.

Please check the below picture and the attached pbix file.

 

Untitled.png

 

Progress measure: = 
VAR _IDtablewithprogressmeasure =
    IF (
        HASONEVALUE ( Data[ValueID] ),
        SWITCH (
            TRUE (),
            MAX ( Data[Duedate] ) > TODAY ()
                && MAX ( Data[Completedate] ) = 0, "In progress",
            MAX ( Data[Completedate] ) > 0, "Done",
            "Overdue"
        )
    )
RETURN
    IF (
        _IDtablewithprogressmeasure IN VALUES ( 'Progress table'[Progress] ),
        _IDtablewithprogressmeasure
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


First it's a pleasure see you answer my question !!!
I did a quick fix, It's works but your solution is by far Better than it.
_Tabfilt =

SUMMARIZE(Add1,Add1[value.title],"StartDateTime",[StartDatetime],"DueDate",[Date_Deadline],"Progress",[Progress],"MaxCompleteDate", [Max CompleteDatetime],"Satus", [Status])

 

I'm having a problem with parsing when MAX(Duedate), I was taking into account that whenever there was a change in the deadline, it would be for the reason of extending the deadline. However, the deadline may be shortened.
I need a formula that returns the latest Duedate, so I can fit it into the Progress formulas. Kind like the image below.
In the case V01 would have to return, Duedate 07/25/2022, because the Updatetime and the time of the update is more recent, but I'm not sure how to do it using measurement.

duadateschanges V4.png

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors