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