cancel
Showing results for
Did you mean:
Highlighted
Helper V

## Filter with 4 possible outcomes

Hi all, I have a question regarding an combined IF-statement or maybe a swich if that is more efficient. I have this invoice data with following important collomns for this calculation (will also provide some sample data at the bottom):

issue_date = date invoice was sent
due_date = date when invoice has to be paid
paid_at = date when client has paid
paid = IF(fact_invoices[paid_at] == "", "NO", "YES")
Paid on time? = IF(fact_invoices[paid_date] < fact_invoices[due_date], "YES", "NO")

Then I have a column "Open & late" with following possibilities:
Already paid: IF(fact_invoices[paid_at] == "", "NO", "YES")
Not yet paid & late:
IF(TODAY() > fact_invoices[due_date]
Not yet paid but customer has time: IF(TODAY() < fact_invoices[due_date]

In one formula this looks liks:
IF(fact_invoices[Paid] == "YES", "Already Paid", IF(TODAY() > fact_invoices[due_date], "Not yet paid & Late", "Not yet paid but customer has time"))

Now I would like to add another possibility to this that says "Not paid" so that this filter/column replaces the "Paid" column/filter. I would like to call this "Status":
Already paid: return all values where fact_invoices[paid_at] IS NOT NULL
Not paid: return all values where fact_invoices[paid_at] is empty
Not yet paid & late: IF(TODAY() > fact_invoices[due_date]
Not yet paid but customer has time: IF(TODAY() < fact_invoices[due_date]

Any one has any idea on how to do this?

What would be even cooler is if I could have one filter that contains everything:

Already paid: return all values where fact_invoices[paid_at] IS NOT NULL
Not paid: return all values where fact_invoices[paid_at] is empty

Already paid & on time: return all values where fact_invoices[paid_at] IS NOT NULL & fact_invoices[paid_at] < fact_invoices[due_date]

Already late & too late: return all values where fact_invoices[paid_at] IS NOT NULL & fact_invoices[paid_at] > fact_invoices[due_date]
Not yet paid & late: IF(TODAY() > fact_invoices[due_date]
Not yet paid but customer has time: IF(TODAY() < fact_invoices[due_date]

Sample data

What I want to avoid is the use of too many filters:

Probably best to use SWITCH I guess but that is very new to me. Or if there is like a totally better way to di this, please let me know! I wish I could add a .pbix file but it is full of confidential information and I don't know how to get an excel from this. If anything is unclear let me know.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Filter with 4 possible outcomes

1. Create Columns.

``Paid? = IF ( fact_invoices[paid_at] <> BLANK (), "Already paid", "Not paid" )``
``````On time or late =
IF (
fact_invoices[paid_at] <> BLANK ()
&& fact_invoices[paid_at] <= fact_invoices[due_date],
IF (
fact_invoices[paid_at] <> BLANK ()
&& fact_invoices[paid_at] > fact_invoices[due_date],
IF (
TODAY () > fact_invoices[due_date],
"Not yet paid & late",
IF ( TODAY () < fact_invoices[due_date], "Not yet paid but customer has time" )
)
)
)
``````

2. Create a Paid Filter table.

``````Paid Filter =
UNION (
VALUES ( fact_invoices[Paid?] ),
VALUES ( fact_invoices[On time or late] )
)
``````

3. Create a Measure.

``````Filter Measure =
SWITCH (
TRUE (),
SELECTEDVALUE('Paid Filter'[Paid?])=BLANK(),1,
SELECTEDVALUE ( 'Paid Filter'[Paid?] ) IN VALUES ( fact_invoices[Paid?] ), IF (
MAX ( fact_invoices[Paid?] ) = SELECTEDVALUE ( 'Paid Filter'[Paid?] ),
1
),
SELECTEDVALUE ( 'Paid Filter'[Paid?] )
IN VALUES ( fact_invoices[On time or late] ), IF (
MAX ( fact_invoices[On time or late] ) = SELECTEDVALUE ( 'Paid Filter'[Paid?] ),
1
)
)``````

4. Put the measure above on the visuals you want to filter and set it =1.

5. Test.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

14 REPLIES 14
Community Support

## Re: Filter with 4 possible outcomes

Please share me the sample data in Excel.

Best Regards,

Icey

Helper V

## Re: Filter with 4 possible outcomes

@Icey sorry for late answer, I don't know how to share files in here so I provide a wetransfer link:

Does this work?

Community Support

## Re: Filter with 4 possible outcomes

Sorry, I have no right to agree here. Please share me the file using other tools, like OneDrive for Business.

Best Regards,

Icey

Helper V

## Re: Filter with 4 possible outcomes

Community Support

## Re: Filter with 4 possible outcomes

1. Create Columns.

``Paid? = IF ( fact_invoices[paid_at] <> BLANK (), "Already paid", "Not paid" )``
``````On time or late =
IF (
fact_invoices[paid_at] <> BLANK ()
&& fact_invoices[paid_at] <= fact_invoices[due_date],
IF (
fact_invoices[paid_at] <> BLANK ()
&& fact_invoices[paid_at] > fact_invoices[due_date],
IF (
TODAY () > fact_invoices[due_date],
"Not yet paid & late",
IF ( TODAY () < fact_invoices[due_date], "Not yet paid but customer has time" )
)
)
)
``````

2. Create a Paid Filter table.

``````Paid Filter =
UNION (
VALUES ( fact_invoices[Paid?] ),
VALUES ( fact_invoices[On time or late] )
)
``````

3. Create a Measure.

``````Filter Measure =
SWITCH (
TRUE (),
SELECTEDVALUE('Paid Filter'[Paid?])=BLANK(),1,
SELECTEDVALUE ( 'Paid Filter'[Paid?] ) IN VALUES ( fact_invoices[Paid?] ), IF (
MAX ( fact_invoices[Paid?] ) = SELECTEDVALUE ( 'Paid Filter'[Paid?] ),
1
),
SELECTEDVALUE ( 'Paid Filter'[Paid?] )
IN VALUES ( fact_invoices[On time or late] ), IF (
MAX ( fact_invoices[On time or late] ) = SELECTEDVALUE ( 'Paid Filter'[Paid?] ),
1
)
)``````

4. Put the measure above on the visuals you want to filter and set it =1.

5. Test.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

## Re: Filter with 4 possible outcomes

Is this problem solved?

Best Regards,

Icey

Helper V

## Re: Filter with 4 possible outcomes

@Icey yes sorry I haven't been able to test your suggestion yet. I will try it next week again when I get back to work. Sorry for the late/no reaction, I will let you know something next week, excuses!

Helper V

## Re: Filter with 4 possible outcomes

@Icey  first of all sorry for the late reaction. I was just trying your solution and I get an error with the measure. See image. Fact tables are renamed from "fact_invoices" to "fact-invoices"

Measure

Calculates columns:

table:

Community Support

## Re: Filter with 4 possible outcomes

Best Regards,

Icey

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors