Help with filter and measures

[Anonymous]

Hello Community,

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

For example I have this table:

Name	Food	Date
Raul	Bread	01/06/2020
Raul	Bread	01/06/2020
Raul	Bread	01/06/2020
Rogerio	Bread	01/06/2020
Rogerio	Bread	01/06/2020
Beto	Bread	01/06/2020
Rogerio	Bread	01/05/2020
Rogerio	Bread	01/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:

Food	Count	Count Food
Bread	3	3

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?

[Icey]

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

[harshnathani]

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]

Hello @harshnathani ,

Thanks for the answer.

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

The Data:

Name	Food	Date
Beto	Bread	09/07/2020 09:20
Raul	Bread	20/07/2020 09:20
Raul	Bread	14/07/2020 09:20
Raul	Bread	13/07/2020 09:20
Raul	Bread	06/07/2020 09:20
Raul	Bread	21/06/2020 09:20
Raul	Bread	30/07/2020 09:20
Raul	Bread	29/07/2020 09:20
Raul	Bread	28/07/2020 09:20
Raul	Bread	27/07/2020 09:20
Raul	Bread	22/07/2020 09:20
Raul	Bread	21/07/2020 09:20
Raul	Bread	15/07/2020 09:20
Raul	Bread	23/06/2020 09:20
João	Bread	03/07/2020 09:20
João	Bread	02/07/2020 12:20
Rogerio	Bread	25/06/2020 09:20
Rogerio	Bread	24/06/2020 09:20
Rogerio	Bread	13/07/2020 09:20
Rogerio	Bread	09/07/2020 09:20
Rogerio	Bread	30/06/2020 11:20
Rogerio	Bread	23/07/2020 09:20
Rogerio	Bread	16/07/2020 09:20
Rogerio	Bread	22/07/2020 07:20
João	Bread	03/07/2020 09:20

[az38]

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))

[Anonymous]

Hello @az38 ,

Thanks for joining us and helping me with the solution.

I did what you said, but didn't show the number of total of bread when I filter, follow the image:

[az38 · Accepted solution]

@Anonymous 

for me it works. check my attached pbix