topic Need to a filter in a formula in DAX Commands and Tips

Need to a filter in a formula

Samhunt — Sat, 17 Feb 2024 09:19:17 GMT

Hi all,

So my problem is the following,

We have properties that we manage and properties that we rent. We want to count all the managed properties.
In my table, I have a Column Called "Management Status" which has 3 different statuses, "Nothing", "Project" and "Management"

I want my formula to count all properties with a Management Status "Management"

I have the following formula that counts everything at the moment

"Total No. Of Active Properties=

VAR CurrentDate = MAX('Calendar Lookup'[Date])

VAR ActiveProperties =

CALCULATE(

COUNTROWS('Active Properties2'),

ALL('Calendar Lookup'),

'Calendar Lookup'[Date] <= CurrentDate,

ISBLANK('Active Properties2'[Deactivation Date])

|| 'Active Properties2'[Deactivation Date]>= CurrentDate

)

RETURN

ActiveProperties"

So I would like to know what adjustment I should do to it to have it only count managed properties.

Regards

Xanthos

Re: Need to a filter in a formula

lbendlin — Sun, 18 Feb 2024 15:33:47 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Need to a filter in a formula

Anonymous — Wed, 21 Feb 2024 09:49:47 GMT

Hi, Samhunt
Based on your description, you can try the MEASURE below. if it doesn't work, please provide the pbix file, datasheet without sensitive data (no screenshots please). Feel free to help you about this problem.

Measure:

Total No. Of Managed Properties = 
VAR CurrentDate = MAX('Calendar Lookup'[Date])
VAR ManagedProperties = 
    CALCULATE(
        COUNTROWS('Active Properties2'),
        ALL('Calendar Lookup'),
        'Calendar Lookup'[Date] <= CurrentDate,
        ISBLANK('Active Properties2'[Deactivation Date])
            || 'Active Properties2'[Deactivation Date] >= CurrentDate,
        'Active Properties2'[Management Status] = "Management"
    )
RETURN
ManagedProperties

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum