Forum Discussion

Jozet777's avatar
Jozet777
Regular Visitor
1 year ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    1 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