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
mhendel
Helper III
Helper III

calculate with text filter

Hello,

 

I have two linked tables : 

 

The first one : 

Capture.PNG

 

For each Projects code, "phase par défaut" is unique.

 

I have the second one called budget, contains an amount of hour per phasis.

 

Tables are linked by code de projet and i have a slicer with that code.

 

Capture.PNG

 

I have to extract the amount of hours for the default phasis, with a measure.  

 

Someting like : budget current phasis = calculate(SUM('budget'[heures prévus]),(a filter to find what i want"))

 

My problem is that i can't find the way to filter that...

 

Can you help me ?

 

Thanks!

 

1 ACCEPTED SOLUTION

@mhendel ,

So here you go:

Budget phase = CALCULATE(sum('public budget'[Heures prévus]),FILTER('public budget','public budget'[Phase de projet]=RELATED('public financial-overview'[Phase par défaut])))
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

19 REPLIES 19
negi007
Community Champion
Community Champion

@mhendel  If your problem still not resolved would apprecite if you could share your powerbi file.




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

@mhendel  Kudos means thumbs up only.

BTW, is that solution working?

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@mhendel ,

Please verify the below Screen shot. If not correct so kindly share the expected output for Budget Phase.

Capture.PNG

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 : what i am expecting to have is the following :

 

For each project code, there is a current phasis (contained in field "phase par défaut" in "Public financial-overview" table.

In the table "Budget", i have to sum "Heures prévus" for each row whare the field "phase de projet" is equal to "phase par défaut".

So doing like this, i have the budget in hours for the current phasis of the project.

 

@mhendel ,

Validate the below Screen shot:

Capture.PNG

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 : yes this is the correct answer 😀

@mhendel ,

So here you go:

Budget phase = CALCULATE(sum('public budget'[Heures prévus]),FILTER('public budget','public budget'[Phase de projet]=RELATED('public financial-overview'[Phase par défaut])))
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 !! Yeah it works!!

 

Thanks for your patience...if i could i'll give you a thousand of kudos :-).

 

Kind regards,

Michael

Perfecta y buena suerte @mhendel !

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@mhendel  Kindly mark my post as a solution.

GOOD LUCK!!!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Tahreem24
Super User
Super User

@mhendel ,

Try using ALLEXCEPT.

= CALCULATE(SUM('budget'[heures prévus]),ALLEXCEPT(TableNAme,TableName[Phase Par defaut]))

NOTE: Put table name and required column name on which you want amount in ALLEXCEPT DAX function.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24  : thanks for your help.

I tried what you wrote me :

Budget phase = CALCULATE(sum('public budget'[Heures prévus]), ALLEXCEPT('public financial-overview','public financial-overview'[Phase par défaut]))
 
And unfortunately i have the following : 
 
Capture.PNG

 

I don' understand why it gives me the default phasis for all the projects and not the one defined by the slicer...
 
Regards
 

@mhendel ,

 

Please update your measure like the below:

Budget phase = CALCULATE(sum('public budget'[Heures prévus]), ALLEXCEPT('public financial-overview','public financial-overview'[Phase par défaut],'public financial-overview'[Code de Project]))

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 thanks again for your halp.

 

I did what you recommend, and i obtain the following erreo : 

 

Capture.PNG

 

Where do i amke a mistake?

 

Regards

@mhendel ,

Show me your measure. I would appreacite your KUODS/THUMBS UP.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Budget phase = CALCULATE(sum('public budget'[Heures prévus]), ALLEXCEPT('public financial-overview','public financial-overview'[Phase par défaut]),'public financial-overview'[Code de projet])

You made little mistake for bracket. 

Budget phase = CALCULATE(sum('public budget'[Heures prévus]), ALLEXCEPT('public financial-overview','public financial-overview'[Phase par défaut],'public financial-overview'[Code de projet]))


Take both the Code de Project and phase par defaut under ALLEXCEPT. Just compare your given measure and mine above measure so you'll get an idea.

I would appreacuate your KUDOS.

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 Thanks.

 

I obtain this :  

 

Capture.PNG

 It s like it is not filtered by the slice...

Btw what is a kudo?

 

Regards

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.