Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
az38
Community Champion
Community Champion

@Anonymous 

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 @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
Community Champion
Community Champion

Hi @Anonymous ,

 

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!

Anonymous
Not applicable

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
az38
Community Champion
Community Champion

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

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

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

az38
Community Champion
Community Champion

@Anonymous 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.