Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to Solution.
Please see the attached PBIX, created a measure that could work
Proud to be a Super User!
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,
Proud to be a Super User!
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!
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?
Proud to be a Super User!
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"}))
Proud to be a Super User!
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:
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"}))
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!
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
Proud to be a Super User!
Hi @Creative_tree88 ,
you can provide a link to a onedrive or google drive location
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
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
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
84 | |
76 | |
49 |
User | Count |
---|---|
143 | |
141 | |
109 | |
69 | |
55 |