Measure to isolate specific field

[Creative_tree88]

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!

[richbenmintz]

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,

[Creative_tree88]

@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!

[Creative_tree88]

@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???

[richbenmintz]

Hi @Creative_tree88 ,

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

[richbenmintz]

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

[Creative_tree88]

@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

[richbenmintz]

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

[Creative_tree88]

@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?

[richbenmintz]

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/ 

[Creative_tree88]

@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.

[richbenmintz]

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

[Creative_tree88]

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

[richbenmintz]

Hi @Creative_tree88 ,

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

[Creative_tree88]

@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 

[richbenmintz]

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

[Creative_tree88]

@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

[richbenmintz]

Hi @Creative_tree88 ,

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

[Creative_tree88]

@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?

[richbenmintz]

Hi @Creative_tree88 ,

Try using the conditional formatting option

[Creative_tree88]

@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