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
ketan10
Resolver I
Resolver I

DAX: Filter products into sold and unsold tables. Need help.

I have a base table as shown below in Power BI.

Base Table.JPG


Using that I need to create two tables in Power BI
1. Sold Products
2. Unsold Products 

These two tables should be filtered based on the 'Date' field.

I need a DAX formula to satisfy the 3 cases shown in the image below to get the desired output in 2 different (SOLD & UNSOLD) tables for all the 3 cases. 
Cases.JPG

Data Link: Here

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @ketan10,

 

Suppose your base table is called 'Base Table'. Create a calendar table.

dim date =
CALENDAR ( MIN ( 'Base Table'[Date] ), MAX ( 'Base Table'[Date] ) )

In 'Base Table', create measures like below:

Total =
CALCULATE (
    SUM ( 'Base Table'[Total Qty Sold] ),
    FILTER (
        'Base Table',
        'Base Table'[Date] >= MIN ( 'dim date'[Date] )
            && 'Base Table'[Date] <= MAX ( 'dim date'[Date] )
    )
)
outside date =
IF (
    MAX ( 'Base Table'[Date] ) >= MIN ( 'dim date'[Date] )
        && MAX ( 'Base Table'[Date] ) <= MAX ( 'dim date'[Date] ),
    1,
    0
)

Pro code1 =
CALCULATE (
    LASTNONBLANK ( 'Base Table'[Product Code], 1 ),
    FILTER (
        'Base Table',
        'Base Table'[Date] >= MIN ( 'dim date'[Date] )
            && 'Base Table'[Date] <= MAX ( 'dim date'[Date] )
    )
)

Pro code2 =
CALCULATE (
    LASTNONBLANK ( 'Base Table'[Product Code], 1 ),
    FILTER (
        'Base Table',
        MAX ( 'Base Table'[Date] ) <= MIN ( 'dim date'[Date] )
            || MAX ( 'Base Table'[Date] ) >= MAX ( 'dim date'[Date] )
            || MAX ( 'Base Table'[Date] ) = BLANK ()
    )
)

flag = IF([Pro code1]=[Pro code2],1,0)

Add 'dim date'[Date] into slicer. Insert two table visuals, in the first one, add field [Product Code] and measure [Total]. In the second one, add field [Product Code]. Also, click the second table visual, add measures [outside date] and [flag] into visual level filters, and set corresponding filter value to 0.

4.PNG

 

Result.

2.PNG

 

I have uploaded pbix file for your reference.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
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

5 REPLIES 5
v-yulgu-msft
Employee
Employee

Hi @ketan10,

 

Suppose your base table is called 'Base Table'. Create a calendar table.

dim date =
CALENDAR ( MIN ( 'Base Table'[Date] ), MAX ( 'Base Table'[Date] ) )

In 'Base Table', create measures like below:

Total =
CALCULATE (
    SUM ( 'Base Table'[Total Qty Sold] ),
    FILTER (
        'Base Table',
        'Base Table'[Date] >= MIN ( 'dim date'[Date] )
            && 'Base Table'[Date] <= MAX ( 'dim date'[Date] )
    )
)
outside date =
IF (
    MAX ( 'Base Table'[Date] ) >= MIN ( 'dim date'[Date] )
        && MAX ( 'Base Table'[Date] ) <= MAX ( 'dim date'[Date] ),
    1,
    0
)

Pro code1 =
CALCULATE (
    LASTNONBLANK ( 'Base Table'[Product Code], 1 ),
    FILTER (
        'Base Table',
        'Base Table'[Date] >= MIN ( 'dim date'[Date] )
            && 'Base Table'[Date] <= MAX ( 'dim date'[Date] )
    )
)

Pro code2 =
CALCULATE (
    LASTNONBLANK ( 'Base Table'[Product Code], 1 ),
    FILTER (
        'Base Table',
        MAX ( 'Base Table'[Date] ) <= MIN ( 'dim date'[Date] )
            || MAX ( 'Base Table'[Date] ) >= MAX ( 'dim date'[Date] )
            || MAX ( 'Base Table'[Date] ) = BLANK ()
    )
)

flag = IF([Pro code1]=[Pro code2],1,0)

Add 'dim date'[Date] into slicer. Insert two table visuals, in the first one, add field [Product Code] and measure [Total]. In the second one, add field [Product Code]. Also, click the second table visual, add measures [outside date] and [flag] into visual level filters, and set corresponding filter value to 0.

4.PNG

 

Result.

2.PNG

 

I have uploaded pbix file for your reference.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks a lot @v-yulgu-msft ! Sorry for the delay in getting back. Your solution works really well.
I optimised it a bit more, as per the requirement. Thanks a lot once again 🙂 

Cheers,
Ketan !

Thyago_Rezende
Resolver I
Resolver I

How can is there product unsold has within "Total Qty Sold" field?

@Thyago_Rezende updated.

Hi,

If your dataset don´t have Date_field for unsold product then it´s impossible filter from Date. Is there no granularity between Sold Products (where there is Date from Sales) and Unsold Products. How can you filter unsold products without Date from Sales?

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.