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
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]) )
)
Thank you your response. Indeed it helped gain a better understanding of my problem.