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