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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors