Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Icey
Community Support
Community Support

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.

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
Icey
Community Support
Community Support

Hi @Anonymous ,

 

What's the meaning? 🤔

 

 

Best Regards,

Icey

Anonymous
Not applicable

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

Paid - Klaas.PNG

 

already paid & late

already paid late - Klaas.PNG

 

Not paid:

Not paid - klaas.PNG

 

Not paid & will be late

Not paid late - Klaas.PNG

 

Not paid & still time

notpaid still time - klaas.PNG

Icey
Community Support
Community Support

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
Not applicable

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
Community Support
Community Support

Hi @Anonymous ,

 

Is this problem solved?

 

 

Best Regards,

Icey

Anonymous
Not applicable

@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
Not applicable

@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

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please delete the extra half bracket in your Measure.

Measure.PNG

 

Best Regards,

Icey

Icey
Community Support
Community Support

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.

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.

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please share me the sample data in Excel.

 

 

Best Regards,

Icey

Anonymous
Not applicable

@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
Community Support
Community Support

Hi @Anonymous ,

 

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

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.