Forum Discussion
Negate Table and Bar Chart count
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
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
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!
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!
