cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KlaasWouters Helper V
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

Megafilter.PNG

 

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

Toomuchfilters.PNG


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.

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Filter with 4 possible outcomes

Hi @KlaasWouters ,

 

Please check:

 

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],
    "Already paid & on time",
    IF (
        fact_invoices[paid_at] <> BLANK ()
            && fact_invoices[paid_at] > fact_invoices[due_date],
        "Already late & too late",
        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.

filter measure.PNG

 

5. Test.

paid filter.gif

 

 

Best Regards,

Icey

 

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

View solution in original post

14 REPLIES 14
Community Support
Community Support

Re: Filter with 4 possible outcomes

Hi @KlaasWouters ,

 

Please share me the sample data in Excel.

 

 

Best Regards,

Icey

KlaasWouters Helper V
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:

 

https://wetransfer.com/downloads/0ed949cc980c571638763c9755fed3ee20200409115227/810c117b2e8bb89d0c59...

 

Does this work?

Community Support
Community Support

Re: Filter with 4 possible outcomes

Hi @KlaasWouters ,

 

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

agree.PNG

 

Best Regards,

Icey

KlaasWouters Helper V
Helper V

Re: Filter with 4 possible outcomes

Community Support
Community Support

Re: Filter with 4 possible outcomes

Hi @KlaasWouters ,

 

Please check:

 

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],
    "Already paid & on time",
    IF (
        fact_invoices[paid_at] <> BLANK ()
            && fact_invoices[paid_at] > fact_invoices[due_date],
        "Already late & too late",
        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.

filter measure.PNG

 

5. Test.

paid filter.gif

 

 

Best Regards,

Icey

 

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

View solution in original post

Community Support
Community Support

Re: Filter with 4 possible outcomes

Hi @KlaasWouters ,

 

Is this problem solved?

 

 

Best Regards,

Icey

KlaasWouters Helper V
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!

KlaasWouters Helper V
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

Capture.PNGMeasure.PNG

 

Calculates columns:

On time or late.PNGPaide.PNG

 

table:

Paid filter.PNG

Community Support
Community Support

Re: Filter with 4 possible outcomes

Hi @KlaasWouters ,

 

Please delete the extra half bracket in your Measure.

Measure.PNG

 

Best Regards,

Icey

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors