Negate Table and Bar Chart count

[Jozet777]

Dear Forum,

Thank you for your support. I have a simple inquiry, jet I cannot get it to work, hopefully someone can illuminate me.

Currently I have a Dashboard comprissing of a purchase table with the following fields:

• Fruit Name
• color
• quantity
• purchase date
• availability date

apple	red	1	05/01/2024	01/01/2024
pear	green	2	05/01/2024	01/01/2024
orange	yellow	4	05/01/2024	01/01/2024
banana	yellow	5	05/01/2024	01/01/2024
cherry	red	6	05/01/2024	01/01/2024
apple	red	2	10/01/2024	01/01/2024
pear	green	4	10/01/2024	01/01/2024
tangerine	orange	7	10/01/2024	07/01/2024
orange	orange	6	10/01/2024	01/01/2024
banana	yellow	8	10/01/2024	01/01/2024
pear	green	3	12/01/2024	01/01/2024
watermellon	green	5	12/01/2024	01/01/2024
tangerine	orange	5	12/01/2024	07/01/2024
grape	green	2	12/01/2024	11/01/2024
peach	yellow	8	12/01/2024	11/01/2024
banana	yellow	7	12/01/2024	01/01/2024
cherry	red	9	12/01/2024	01/01/2024

And a fruit names and availability dates:

apple	01/01/2024
pear	01/01/2024
watermellon	01/01/2024
orange	01/01/2024
banana	01/01/2024
cherry	01/01/2024
tangerine	07/01/2024
grape	11/01/2024
peach	11/01/2024

I have other tables with the following information:

• Purchase Dates (a Distinct list of purchase dates)
• Colors (red,gree,yellow)

I'm trying to get a dashboard with the following information:

• A slicer with the purchase dates
• A complete list of fruits available on the selected date from the slicer
• A list of fruits purchased on the selected date
• A list of fruits that were not purchased but available on the selected date
• A Bar Chart listing the number of purchased fruits and not purchased fruits, with the X value as color

So far I have achieved the necessary relashionships to filter by purchase dates, and a meassure that provides me with the purchased and not purchased fruits.

However, roaming through the forums, I cannot figure out why I cannot get my barchart to work. Additionally, I'm still figuring out how to filter per availability date.

Here's a video of what I have so far:

Any guidance would be greatly appreciated!

[Anonymous · Accepted solution]

Thanks for the reply from amitchandak , please allow me to provide another insight: 
Hi  @Jozet777 ,

Here are the steps you can follow：

A slicer with the purchase dates:

Use Table1's [purchase date] as the slicer:

A list of fruits purchased on the selected date:

Use Table1's [Fruit Name].

A list of fruits that were not purchased but available on the selected date:

Create a measure:

Flag =
var _select=SELECTCOLUMNS(ALLSELECTED('Table1'),"date",[purchase date])
var _test=CONCATENATEX(FILTER(ALLSELECTED('Table1'),[purchase date]=MAX([purchase date])&&[purchase date] in _select),[Fruit Name],"-")
RETURN
IF( CONTAINSSTRING( _test,MAX('Table2'[Fruit Name]))=FALSE(),1,0)

Place [Flag]in Filters, set is=1, apply filter.

A Bar Chart listing the number of purchased fruits and not purchased fruits, with the X value as color:

Create measure:

purchased fruits =
var _select=SELECTCOLUMNS(ALLSELECTED('Table1'),"date",[purchase date])
RETURN
CALCULATE(
    DISTINCTCOUNT('Table1'[Fruit Name]),
    FILTER('Table1',
    [purchase date] in _select))

not purchased fruits =
var _select=SELECTCOLUMNS(ALLSELECTED('Table1'),"date",[purchase date])
var _test=CONCATENATEX(FILTER(ALLSELECTED('Table1'),[purchase date]=MAX([purchase date])&&[purchase date] in _select),[Fruit Name],"-")
RETURN
CALCULATE(
    DISTINCTCOUNT('Table2'[Fruit Name]),
    FILTER('Table2',CONTAINSSTRING( _test,'Table2'[Fruit Name])=FALSE()))

Best Regards,

Liu Yang

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

[Jozet777]

Thank you again for your response!

Just one more question, in order to include the availability date so that only fruits listed on the NOT Purchased with an availability date >= than the Slected Purchase date are listed?

For example,  in the following image it should only show Cherry, since grape, peach, and watermellos were not available at that purchase date (10-01-2024).

I tried modifying the filters the following way, but it doesn't seem to work.

//Meassure at the purchase_dates table:
selected_date = SELECTEDVALUE(purchase_dates[purchase_date])"

//Meassure at the fruit_purchase table:
Flag = 
var _select = SELECTCOLUMNS(ALLSELECTED(fruit_purchase),"date",fruit_purchase[purchase_date])
var _test = CONCATENATEX(FILTER(ALLSELECTED(fruit_purchase),fruit_purchase[purchase_date] = MAX(fruit_purchase[purchase_date])&&(fruit_purchase[purchase_date] in _select)&&fruit_purchase[purchase_date]>purchase_dates[selected_date]),fruit_purchase[name],"-")
RETURN
IF(CONTAINSSTRING(_test,MAX(fruits[name]))=FALSE(),1,0)

I tried adding the same expression fruit_purchase[purchase_date]>purchase_dates[selected_date] to the not purchased fruits and it didn't work. Perhaps do you have any more advice. Thank you very much for your help!

[Jozet777]

This is incredible. Thank you very much for your response. I'll just add the availability date filter to the DAX formulas you've provided and that would solve my issue. I just required a further understanding of the issue. Thank you again!

[amitchandak]

@Jozet777 , Create only one date table and keep both join inactive. It measn you always need userelationship in the measure to work.

Purchased Fruits =
CALCULATE(
DISTINCTCOUNT( Purchase[Fruit Name] ),
USERELATIONSHIP( 'Date'[Date], Purchase[Purchase Date] )
)

Available Fruits =
CALCULATE(
DISTINCTCOUNT( Purchase[Fruit Name] ),
USERELATIONSHIP( 'Date'[Date], Purchase[Availability Date] )
)

Not Purchased =
[Available Fruits] - [Purchased Fruits]

or

Not Purchased =

CALCULATE(
DISTINCTCOUNT( Purchase[Fruit Name] ),
filter(Purchase, Purchase[Availability Date] in values(Date[Date]) && not Purchase[Purchase Date] in values(Date[Date]) )
)

[Jozet777]

Thank you your response. Indeed it helped gain a better understanding of my problem.