How to hide first entry (open sale status) after final occurs?

[mbarros]

Hello community!
I'm really struggling to figure this out, so your help will be greatly appreciated!

Here is the situation: my dataset in Power BI have 3 types of sale: one is direct sale, and the other has 2 steps: first half and second half (at a later date, see screenshot below from excel for simplicity, but I need this in Power BI).

The calculated column (Status, column F) below was running perfectly until a new situation came up.

Status column should hide the first "half" when the "2nd half" happens (e.g. cells F2 and F6), this is perfect.

The problem is when there is another "half" for the same Company (row 8). Cell F8 is empty, but it was supposed to display "half", because the 2nd half (cell F7) was closing the first "half" (and at an earlier date). Also, this Business D may or may not have more halves in the future, so need the formula to reflect that. The new code should look like Column G. Makes sense?

This calculated column is important so I can quickly identify with filters the customers that are pending the 2nd half sale (e.g. F5).

Here are my Power BI calculated columns. Really appreciate your help!

ID = CONCATENATE([Company], [Type of Sale])

Status = IF(Table1[Type of Sale]="half", IF(IFERROR(LOOKUPVALUE(Table1[ID],Table1[ID],CONCATENATE(Table1[Company],"2nd half")),0)>0,"",Table1[Type of Sale]),Table1[Type of Sale])

[v-shex-msft · Accepted solution]

HI @mbarros,

I'll try to summarize your description to realize your requirement.
According to your description, you mean you want to hide the first 'half' status if the same company has 2nd half status in the following records except if they are no 2nd half statue existed in the following records, right?

If this is a case, you can check the following records with 2nd half stats to achieve your requirement.

Status =
IF (
    Table1[Type of Sale] = "half",
    IF (
        COUNTROWS (
            FILTER (
                ALLSELECTED ( Table1 ),
                [Date] > EARLIER ( Table1[Date] )
                    && [Company] = EARLIER ( Table1[Company] )
                    && Table1[Type of Sale] = "2nd half"
            )
        ) > 0,
        BLANK (),
        Table1[Type of Sale]
    )
)

Regards,

Xiaoxin Sheng

[mbarros]

Thank you so much for this code @v-shex-msft  ! Really appreciate it.

I was confident your formula was going to work, but I got the error message "There's Not Enough Memory To Complete This Operation" and I'm trying to troubleshoot since then.

Even tho my dataset has thousands of records, the file is only 45MB (my pc has 16gb ram and i7, PBI is the 64 bit). Will let you know if I can solve this.

[v-shex-msft]

Hi @mbarros,

Any other applications processing in the backend of your system that causes the not enough memory resource to calculate in DAX expressions?

BTW, what version of the desktop are you test? Can you please share some dummy data to test?
Regards,

Xiaoxin Sheng

[mbarros]

I'm using the latest August PBI version.
Unfortunately I'm not able to share the file or a dummy data, it has sensitive sales data. But I was able to grab a portion of the database and run another PBI file, and your code works perfectly, thank you so much!!

Now I'm trying to figure out how to make this work in the original dataset. To your question: I didn't have any other application open. And checking the task manager, PBI was using all my available ram (~12GB) which is crazy high.

[mbarros]

Hey @v-shex-msft 
Quick update, I was able to create the calculated column but I had to do it in the server (remote desktop) which has like 64GB of ram lol
Thank you so much for helping out.

[pranit828]

Hi @mbarros 

Have you tried pivot columns in this case. This should give you three columns in all case.

I would go by this approach and check for NULL data in these column to further my analysis.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit

[mbarros]

Thanks for replying @pranit828 
I'm not sure how I would use pivot columns in this case (newbie here).
Could you please elaborate a bit more on that? I appreciate it.

[pranit828]

Hi @mbarros 

Pivot and unpivot columns is a very interesting topic and can be used in Excel as well as Power BI.

Here is the link to one such video. Let me know if it helps or I can get some more details for you.

https://www.youtube.com/watch?v=V_ULyeHNJFY

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit

[mbarros]

Thanks for the video @pranit828 

Now I understand what pivot column can do, but still unsure how it can help with my problem =(