need help to use Date Slicer's outside range & within range to create dynamic table

[Anonymous]

OBJECTIVE: 

1. The Items which were not within the date range of 'Date' Slicer (including items without Date) must be included to the UNSOLD Table while

2. The Items within the date range of 'Date' Slicer must appear on the SOLD Table.

Below is the Base Table 

Using the Base Table,  I need to create two dynamic tables in Power BI:

1. SOLD Items Table - showing 'Date', 'Invoice No'., 'Item Code', 'Item Name' and 'Qty'

2. UNSOLD Items Table - showing only the unique 'Item Code' and 'Item Name'

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

Please help me to have a DAX Formula to satisfy the 3 Cases below to create the two tables: SOLD TABLE & UNSOLD TABLE

The link to the Data in MS Excel is here: 

https://drive.google.com/drive/folders/1eJclmIpGUWp1IL62DjnOy2aGSB_5rtFT?usp=sharing

[v-yuta-msft]

@Anonymous,

I'm afraid table or column generated by dax is in data model which can't be changed by the slicer chart in report level. But as a workaround, you can use measure and visual to generate the "Sold Table" and "Unsold Table".

To generate the "Sold Table", you can simply drag Date, Invoice No., Item Code, Item Name and Qty to the table chart. To generate the "Sold Table", We can create two measures using DAX formula below:

Item Code Measure = 
CALCULATE (
    MIN ( Sheet1[Item Code] ),
    FILTER ( Sheet1, NOT ( Sheet1[Date] IN ALLSELECTED ( 'Date'[Date] ) ) )
)

Item Name Measure = 
CALCULATE (
    MIN ( Sheet1[Item Name] ),
    FILTER ( Sheet1, NOT ( Sheet1[Date] IN ALLSELECTED ( 'Date'[Date] ) ) )
)

Then drag the two measures to the table chart, however, if just drag the two measures to table chart, you will see only one rows shows the minimun value of Item Code and Item Name, so I would suggest you to create an index column and drag the index column to the table visual too. The result will be like below:

 

You may also refer to the appendix.

Community Support Team _ Jimmy Tao

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

[Anonymous]

hi,

thank you so much for the fast response.

I tried the pbix you attached, the Unsold Table works but the Sold Table doesn't.

I really appreciate if you could help me with the Sold table as well.

[v-yuta-msft]

@Anonymous,

---

thank you so much for the fast response.

I tried the pbix you attached, the Unsold Table works but the Sold Table doesn't.

I really appreciate if you could help me with the Sold table as well.

---

Could you clarify more details about "Sold Table not work"?

Community Support Team _ Jimmy Tao

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

[Anonymous]

hi Jimmy, thank you for your question.

I am referring to the pbix attached and I checked it, the Sold Table doesn't change when filters are applied. 

[v-yuta-msft]

@Anonymous,

You could build the Sold Table in another report page.

Community Support Team _ Jimmy Tao

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

[v-yuta-msft]

@Anonymous,

Actually, there's something wrong with the relationship in the PBIX I have provided, however, you can create the Sold_Table by creating two measures similarly like below:

Item Code Measure = 
CALCULATE (
    MIN ( Sheet1[Item Code] ),
    FILTER ( Sheet1, Sheet1[Date] IN ALLSELECTED ( 'Date'[Date] ) ) 
)

Item Name Measure = 
CALCULATE (
    MIN ( Sheet1[Item Name] ),
    FILTER ( Sheet1, Sheet1[Date] IN ALLSELECTED ( 'Date'[Date] ) ) 
)

Community Support Team _ Jimmy Tao

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