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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

PowerBi DIstinct and IF formula

Hi everyone,

 

I'm a bit stuck with a report I'm trying to build.

I have the below columns where I'm trying to find for how many days it was outstanding.

GeorgeGabaroi_1-1686658270556.png

 

 

I've already created new columns for this in PBI where if outstanding (Status still open and I calculate Report Date - Created date) between 1-4 working days results in "good", if 5-10 working days - "amber" and everything higher is "red"

 

The problem I have is :

                       - For example, the invoice with ID 0123, created on 05/06/2023, shows on every report for 05/06, 06/06, 07/06, 08/06 as "good" as in all those days it was created within 4 working day, however on 09/06/2023 it will show as "amber".

               Assuming I only have 1 invoice on my report (ID 0123), and if I filter the report for last week, I will see that 1 invoice was "good", 1 invoice was "amber", where I would like to see that last week I only had 1 invoice and that was in "amber"

 

Essentially if it was good and now amber, I want to see it as amber now based on each distinct invoice ID. If it was good, then amber and now red, see it as red.

I've tried the below formula to create a new column, however I don't know how to integrate this one or other formula in order to include the Distinct of Invoice ID

 

IF(AND(CONTAINS(Sheet1, Sheet1[Outstanding invoice],"good"), CONTAINS(Sheet1, Sheet1[Outstanding invoice],"amber")), "amber",
IF(AND(CONTAINS(Sheet1, Sheet1[Outstanding invocie],"amber"), CONTAINS(Sheet1, Sheet1[Outstanding invoice],"red")), "red", Sheet1[Outstanding invoice]))

                                     

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous ,

I have created a simple sample , please refer to my pbix file to see if it helps you.

Create a column first.

weeknum = 
          WEEKNUM('Table'[Reprt Date],1)

Then create 2 measures.

Measure =
VAR _1 =
    DATEDIFF ( MAX ( 'Table'[Invoice Creaated] ), MAX ( 'Table'[Reprt Date] ), DAY )
VAR _2 =
    IF (
        _1 <= 3
            && MAX ( 'Table'[status] ) = "Open",
        "Good",
        IF (
            _1 <= 9
                && _1 >= 4
                && MAX ( 'Table'[status] ) = "Open",
            "Amber",
            IF ( MAX ( 'Table'[status] ) = "Open" && _1 > 9, "Red", BLANK () )
        )
    )
RETURN
    SWITCH ( TRUE (), _2 = "Good", 1, _2 = "Amber", 2, _2 = "Red", 3, BLANK () )
result =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[weeknum] = SELECTEDVALUE ( 'Table'[weeknum] )
                && 'Table'[invoice ID] = SELECTEDVALUE ( 'Table'[invoice ID] )
        ),
        [Measure]
    )
RETURN
    IF ( _1 = 1, "Good", IF ( _1 = 2, "Amber", IF ( _1 = 3, "Red", BLANK () ) ) )

 

vrongtiepmsft_0-1686795167297.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

Anonymous
Not applicable

Hi,

 

Thanks for taking you time and for your reply.

Unfortunately this didn't solve the issue from multiple considerents. 

1 - I will need to take in consideration only working days when deciding if between 1-4 days, 5-10 etc.

2 - I can't use the measure you've created as I will need this to be in a cluster column chart. What I'm trying to achieve is something like below (FYI, below are multiple invoice references, for a specific agent)

GeorgeGabaroi_0-1687189264939.png

 

The issue with the above, is that Invoice ID0123 for example, was good but now is amber and it still counts as 1 for both of them. If I filter the cluster by that invoice, I get the below, when I would like to have only 1 cluster, saying 1 as Amber (if filtered for ID0123)

 

GeorgeGabaroi_1-1687189356205.png

 

I've attached screenshots with raw data and PBI as it doesn't let me attaching them here

 

GeorgeGabaroi_0-1687190387626.png

 

 

GeorgeGabaroi_1-1687190427776.png

 

GeorgeGabaroi_2-1687190434132.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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