Filter with 4 possible outcomes

[Anonymous]

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.

Thanks in advance!

[Icey]

Hi @Anonymous ,

What's the meaning? 🤔

Best Regards,

Icey

[Anonymous]

Aah I some of my text got deleted. I wanted to say that the filter is working but that it has different outputs than what I expected. I compared my results to yours and they were a bit different. That is what I wanted to show you in the pictures but it didn't came out as planned. I see only your pictures came through so I'll add mine in this post, hope that works.

Well the general meaning is to track customer payment time and have it in a user-friendly way whitout having too much filters on the top if that mat concern you as well. But I think we might stick to having 3 different filters tho, makes the rest a bit simpler...

already paid

already paid & late

Not paid:

Not paid & will be late

Not paid & still time

[Icey]

Hi @Anonymous ,

Well, you can choose to use any method that you think is simpler.😉

And what's the axis of the stacked bar charts?

Best Regards,

Icey

[Anonymous]

Well pure for the user having one filter is easier. But once I leave this project I also mist be able to explain what I have build and how they can change this later if needed. So maintenance wise the 3 filter approach would be simpler. which one to use is something I must yet decide, if I can figure out how ur measure works I moght use that one tho!

For your question:

Axis = Company name 

Legend= +5 days late or -5days late

Value = count of paid_on_time

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Anonymous]

@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!

[Anonymous]

@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:

[Icey]

Hi @Anonymous ,

Please delete the extra half bracket in your Measure.

Best Regards,

Icey

[Anonymous]

Hi @Icey

Link to new sample data:

https://1drv.ms/x/s!AvlpOfUDGFD5tQGVorotbdHten1P?e=GkhDl9

Differences:

already paid:

Already paid & late:

Not paid:

Not paid & will be late:

Not paid & still time:

[Icey · Accepted solution]

Hi @Anonymous ,

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.

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.

[Icey]

Hi @Anonymous ,

Please share me the sample data in Excel.

Best Regards,

Icey

[Anonymous]

@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?

[Icey]

Hi @Anonymous ,

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

Best Regards,

Icey

[Anonymous]

@Icey Does this work?

https://1drv.ms/x/s!AvlpOfUDGFD5s24bCjHd7zi1qQWN?e=nAcrcF