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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Creative_tree88
Helper IV
Helper IV

Measure to isolate specific field

Hi all - new to this!  have been using Qlikview for years and now we are transferring over to PowerBI so need to brush up on syntax used for certain measures.

 

I use the measure = DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]) to calculate the count of clients but there are two elements to this count - Activity and Demand.  if I just use the above measure, it counts everything and what I need is to show tables with separated out counts for Activity and separate counts for Demand as this is how we measure everything!  A total count actually doesn't make any sense at all for us.

 

My Qlikview set analysis would look like this:

 

=count({<Activity_or_Demand={'Activity'},Financial_Year-={'2099'},Room-={'CTSIM','CTSIM2'},Individual_Exam_Parts_Per_Event-={'CLDTH'}>}Event_Key)

 

The above is exactly what I need, but in Power BI talk!  Anyone able to shed some light?  Once this is cracked, it's out bread and butter for measuring everything really.

 

many thanks peeps!

1 ACCEPTED SOLUTION

Please see the attached PBIX, created a measure that could work

 

richbenmintz_0-1632879105095.png

 



I hope this helps,
Richard

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

Proud to be a Super User!


View solution in original post

29 REPLIES 29
richbenmintz
Solution Sage
Solution Sage

Hi @Creative_tree88 ,

 

I think the following measures  will provide a starting point.

Activity Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Activity")

Demand Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Demand")

Hope this helps,



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz

 

Thanks so much!  Presume the rest of the statement i.e

 

=count({<Activity_or_Demand={'Activity'},Financial_Year-={'2099'},Room-={'CTSIM','CTSIM2'},Individual_Exam_Parts_Per_Event-={'CLDTH'}>}Event_Key)

 

is done in the same way.  In Qlikview, to exclude a value it is written -= but I'm guessing PowerBi is different?  Having been so used to Qlikview lingo, it's just a case of getting the basics down, then I should be OK.  Appreciate the really quick reply, thanks v much!

@richbenmintz

 

That's got it - just having a little bit of a hang up on the <>2099 part.  I guess it could be down to not recognising the formatting???

Hi @Creative_tree88 ,

If the [financial_year] is a number then remove the Quotes. otherwise what is the error you are receiving? 



I hope this helps,
Richard

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

Proud to be a Super User!


 

Hi @Creative_tree88 

 

roughly translated,

Activity Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Activity", [Financial_Year]<>"2099",Individual_Exam_Parts_Per_Event<>"CLDTH", NOT (Room IN {"CTSIM","CTSIM2"}))

 

 



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz 

Many thanks for your help with this - I've just about got there with it, but now want to calculate an average per month, using the same type of exclusions etc...my syntax is:

 

Avg Per Month Demand = AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),
'Test Python Exam' [AD_Data]= "Demand",
'Test Python Exam'[AD_Examination_01]<>"CLDTH",
'Test Python Exam'[AD_CalYr]<>2099,
NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))
[Count of AD_Event_Key average per AD_CalYrMth])
 
This doesn't work, surprise surprise...I've managed to get it to work just using the first part i.e. 
 
Avg Per Month Demand = AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),
[Count of AD_Event_Key average per AD_CalYrMth])
 
But when I try to include the 'set analysis' part it falls apart.  Any ideas?  Really appreciate your pointers here.  Kind Rgds

Hi @Creative_tree88 

 

You need to wrapped the averagex inside a calculate like:

Avg Per Month Demand = 
CALCULATE(AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),[Count of AD_Event_Key average per AD_CalYrMth]),
'Test Python Exam' [AD_Data]= "Demand",
'Test Python Exam'[AD_Examination_01]<>"CLDTH",
'Test Python Exam'[AD_CalYr]<>2099,
NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))


I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz 

If I wanted to compare current month, using the above 'Average Per Month' measure syntax - against last months average, how would this look?  Or against the same month, two years ago, or against a specific month such as 2019-03??

Can PowerBI do this easily, without me needing to change the syntax every month to suit?

Hi @Creative_tree88 ,

 

here is a really great reference for you from the wonderful people at SQLBI, they are pretty much the best!

https://www.daxpatterns.com/ 



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz 

Thanks for this - it's a little too complex for my needs at this point, I think.  I already have a calendar table set up and linked, so really just need to get the syntax sorted.  If you can help with this, it would be much appreciated.

Hi @Creative_tree88 ,

 

Power BI has a whole set of Time intelligence functions, https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax , If you provide a link to a pbix file i can show you some examples with your data model



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz 
Apologies - how do I send you a link to a sampl of my work?

Hi @Creative_tree88 ,

you can provide a link to a onedrive or google drive location



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz 
I've attached link to this message.  I've tried to show you what I've done so far, using the date from the website you advised.  Can't seem to master the month on month / previous month / % change etc, so hoping you can show me some examples using this data model.

 

As Previously mentioned, we measure Activity and Demand separately and I'd really like to know, from month to month, even week to week, which areas (Specialty Names) are increasing at the fastest rate / most numbers, on a real and % term basis.  Demand is particuarly useful for this sort of measure.  

 

Many thanks!  Power BI Sample 

Hi @Creative_tree88 ,

 

First thing you need to do is replace the month value in your slicer with the month from your date table, then your count previous month will start to work, attached your file with that simple mod

 

richbenmintz_0-1632433088330.png

 



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz 
That works well, thanks for pointing that out.  Is there any way of adapting this table to show (in the data model I gave you the link for) which 'Modality' has increased the most (in numbers and in % terms) based on last month, last week and this time last year??  If you could show me how this is done, I think I'd be able to roll this out for all the other measures I need to show in my work?  Many thanks for all your help thus far!  Kind regards

Hi @Creative_tree88 ,

I have added a few more measures and added a new calendar that includes weeks. see attached



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz - thanks for this, really helpful indeed!  I am still really struggling to be able to visualise and 'pick out' the 'AD_Modality which has increased the most, over a month or week basis.  How can this be achieved?

Hi @Creative_tree88 ,

 

Try using the conditional formatting option



I hope this helps,
Richard

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

Proud to be a Super User!


@richbenmintz - I don't know how to do that, unfortunately, and relate it to 'AD_Modality'.  Every time I bring AD_Modality into the table, it goes blank so getting confused as to how this is done, given the tables you've done, relate specifically to month and week dates, not AD_Modality fields...bit flummoxed to be honest.  If you had time, I'd really appreciate if you could show me a visual, based on your month change table, which highlights which AD_Modality has increased the most from this month to last month...?  I think once I know how to do this, the rest should be more straight forward.  Kind regards

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.