cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Felipeamartinz
Regular Visitor

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?

 

 

1 ACCEPTED SOLUTION

@Felipeamartinz 

for me it works. check my attached pbix

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
Icey
Community Support
Community Support

Hi @Felipeamartinz ,

 

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
Super User III
Super User III

Hi @Felipeamartinz ,

 

Create a measures

 

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

 

CountName = COUNT('Table'[Name])

 

1.jpg

 

Regards,
Harsh Nathani

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

Hello @harshnathani ,

Thanks for the answer.

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

error.jpg

 

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

Hi @Felipeamartinz 

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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:

2020-07-26 16_52_29-ex - Power BI Desktop.jpg

@Felipeamartinz 

for me it works. check my attached pbix

Снимок.PNG


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.