cancel
Showing results 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

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
Solution Sage

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

I hope this helps,
Richard

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

Proud to be a Super User!

29 REPLIES 29
Solution Sage

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!

Helper III

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

Helper III

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

Solution Sage

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!

Solution Sage

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!

Helper III

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]),
NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))

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

But when I try to include the 'set analysis' part it falls apart.  Any ideas?  Really appreciate your pointers here.  Kind Rgds
Solution Sage

You need to wrapped the averagex inside a calculate like:

``````Avg Per Month Demand =
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!

Helper III

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

Solution Sage

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

I hope this helps,
Richard

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

Proud to be a Super User!

Helper III

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

Solution Sage

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!

Helper III

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

Solution Sage

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!

Helper III

@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

Solution Sage

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

I hope this helps,
Richard

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

Proud to be a Super User!

Helper III

@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

Solution Sage

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!

Helper III

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

Solution Sage

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!

Helper III

@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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors