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 sentdue_date = date when invoice has to be paidpaid_at = date when client has paidpaid = 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 NULLNot paid: return all values where fact_invoices[paid_at] is emptyNot 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 NULLNot paid: return all values where fact_invoices[paid_at] is emptyAlready 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!

Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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.

Thanks in advance!

Need Help

Icey

6 years ago

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.

Filter with 4 possible outcomes.pbix161 KB

14 Replies

Icey
Community Support
6 years ago
Hi Anonymous ,

Please share me the sample data in Excel.

Best Regards,

Icey
- Anonymous
  Not applicable
  6 years ago
  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/810c117b2e8bb89d0c59353ead9e338a20200409115303/e5540d
  
  Does this work?
  - Icey
    Community Support
    6 years ago
    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

Icey

Community Support

6 years ago

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.

Filter with 4 possible outcomes.pbix161 KB

Icey
Community Support
6 years ago
Hi Anonymous ,

Is this problem solved?

Best Regards,

Icey
- Anonymous
  Not applicable
  6 years ago
  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
    6 years ago
    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
Community Support
6 years ago
Hi Anonymous ,

What's the meaning? 🤔

Best Regards,

Icey
- Anonymous
  Not applicable
  6 years ago
  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
    Community Support
    6 years ago
    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