Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with filter and measures

Hello Community,

I'm new in the PBI and need some help from the start.

For example I have this table:

NameFoodDate
RaulBread01/06/2020
RaulBread01/06/2020
RaulBread01/06/2020
RogerioBread01/06/2020
RogerioBread01/06/2020
BetoBread01/06/2020
RogerioBread01/05/2020
RogerioBread01/05/2020

 

I want to show everyone how much of bread in the month each one ate and the total quantity in the month.

I create these measures:

Count = COUNT(Planilha1[Food])
count food = calculate([Count];ALLEXCEPT(Planilha1;Planilha1[Date];Planilha1[Name]))
I create a dashboard with a table Food, Count and Count Food, a filtre of name and a filter of date.
When I filter Raul and and the date 01/06/2020, the table shows:
FoodCountCount Food
Bread33

 

But the Count Food needed to show 6 (Total of 01/06/2020).

I tried to use another measures with all, allselected but didn't work.. Can someone help me please?

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Create a measures

     

    Count Food = CALCULATE(COUNT('Table'[Food]),ALLEXCEPT('Table','Table'[Date]))

     

    CountName = COUNT('Table'[Name])

     

     

    Regards,
    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Anonymous ,

      Thanks for the answer.

      It worked, but after input more data It doesn't work when I filter the data.

       

      The Data:

      NameFoodDate
      BetoBread09/07/2020 09:20
      RaulBread20/07/2020 09:20
      RaulBread14/07/2020 09:20
      RaulBread13/07/2020 09:20
      RaulBread06/07/2020 09:20
      RaulBread21/06/2020 09:20
      RaulBread30/07/2020 09:20
      RaulBread29/07/2020 09:20
      RaulBread28/07/2020 09:20
      RaulBread27/07/2020 09:20
      RaulBread22/07/2020 09:20
      RaulBread21/07/2020 09:20
      RaulBread15/07/2020 09:20
      RaulBread23/06/2020 09:20
      JoãoBread03/07/2020 09:20
      JoãoBread02/07/2020 12:20
      RogerioBread25/06/2020 09:20
      RogerioBread24/06/2020 09:20
      RogerioBread13/07/2020 09:20
      RogerioBread09/07/2020 09:20
      RogerioBread30/06/2020 11:20
      RogerioBread23/07/2020 09:20
      RogerioBread16/07/2020 09:20
      RogerioBread22/07/2020 07:20
      JoãoBread03/07/2020 09:20
      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous 

        1. create a calendar table like

        CalendarTable = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

        2. create relationship between calendar table and Planilha1 table by date field

        3. Add CalendarTable[Date] to date slicer visual

        4. create a measure in Planilha1 table

        Count Food = 
VAR MaxDate = CALCULATE(MAX(CalendarTable[Date]))
VAR MinDate = CALCULATE(MIN(CalendarTable[Date]))
RETURN
CALCULATE(COUNTROWS(Planilha1), FILTER(ALL(Planilha1), Planilha1[Date] >= MinDate && Planilha1[Date] <= MaxDate))
  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Is this problem solved?

     

    If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

     

    If not, please let me know.

     

     

     

    Best Regards,

    Icey