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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Creative_tree88
Helper III
Helper III

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.