Hi. I'm trying to create a measure that will show progress of tasks completed against Milestones.
My data is in a star schema in Power Pivot centred around Milestone as the only Fact table with all other tables Dimension tables - all with different columns an potentially different column headings.
I don't know whether I should be using a DAX formula in PP or merging the data and cleansing in Power Query. I'm new to DAX.
I currently have a dashboard that shows a bar chart of the number of items from each Dimension table against each project Milestone. This works fine, but I want to show a headline metric how many of those items have the status of Complete against each Milestone. I intend to link the measure into a slicer based on Milestone, hence, when Milestone 1 is selected it will show how complete that milestone is.
I'm comfortable setting up the slicer, but need help creating and calculating and best displaying the metric. It should be a simple % of status complete of the total tasks in the selected Milestone.
Thanks in advance.
Hi Francis,
A simple measure should be able to count the completed ones:
CALCULATE(COUNT(SourceTable[Measures]),SourceTable[Status]="Complete")
Another count with all measures should give you the reference for comparison.
Without a sample file, hard to see how data looks, you will have to attach one.
Thanks Catalin.
Each Dimension table is connected to the Milestone Fact table using a Milestone reference. Each Dimension table has a Status column with different values, although most have Complete or Completed as the status I want to count.
All tables are brought into the data model using Power Query. I believe I will need to cleanse the status fields such that they are all called the same (eg Status) and contain the same status values (eg change Completed to Complete where appropriate).
I believe I need two counts, one for the Total number of items per Milestone (across all Dimension tables) and one for the Total number of items with Status = Complete to create a set of pecentages. Probably best to use a PowerPivot table to create this or should I use Dax? This is where I'm getting unstuck. I'm ploughing through the Definitive Guide to Dax book (p70) but it's heavy going.
Then, create a Gauge or similar that is linked into the table and changes when the Milestone is selected.
Probably best to use a PowerPivot table to create this or should I use Dax?
DAX is the language used in PowerPivot, they are not alternatives.
2 separate counts is what I mentioned, you have in the previous message a sample DAX formula for the complete status count, the full count should be the same without the filter:
CALCULATE(COUNT(SourceTable[Measures]))
You have to understand how Power Pivot works before building solutions, it's hard to fly with small wings.
Thanks. I get PP ok but was thinking that as I need to be able to use a slicer on the Milestone I'm going to need a table of counts of the Complete status (ie one for each Milestone) unless that's what your table of Measures is in your example. I'll have a play around with your examples but I've never done this before so patience is a virtue.
Struggling with the first part of your formula... SourceTable(Measures)?
If the table that I'm counting the status from is called BusinessChange how would the DAX look?
eg CALCULATE(COUNT(BusinessChange[????],BusinessChange[Status]="Complete")
What goes in the ???? and where does the result go?
Also, do I have to create a separate measure for each Dimension table status "Complete" count?
I told you I'm a novice. Just need to get my head around it.
Thanks.
CALCULATE(COUNT(BusinessChange[AnyColumnWithoutBlanks],BusinessChange[Status]="Complete")
Like in excel sheet formulas, a COUNT can be applied to any column, as long as there are no blanks, the result will be the same.
where does the result go?
You have to understand how Power Pivot works before building solutions.
PP is not just a pivot table, adding a power pivot table to sheet is not a challenge. Organizing the data model, building relationships and writing measures is the challenge, you have to take into account the entire context: data model relationships, fields used in the pivot, filters context. A measure can work as expected with some fields, but might not work in another pivot context, if you add other fields.
The result goes nowhere, you have to add the measure in the pivot table, the results will be calculated depending on the power pivot context.
Ok thanks.
As I said in my first post, I have my data organised in PP in a Star schema data model centred around Milestone as the Fact table with a number of other Dimension tables sitting around it. The reference tables are all joined to the Milestone table with 1-many relationships with the 1 end going into the Milestone table. They are all joined using a Milestone reference field in the Dimension tables. I'm happy with that. I have also built a dashboard that I am happy with using PP.
What I am trying to achieve is a headline metric that shows overall progress of tasks (ie line items from each Dimension table) where the status in those line items is 'Complete'.
I'm sure to a person who does this all the time it can be done in 5 minutes, but for someone who has never used DAX before it is understanding 'where I put things' is probably the best way I can describe it. Hence, when I create a measure in PowerPivot, do I need to create separate measures for each Dimension table? If so, I guessing that I need to have the table selected from the data model, either in diagram view or have the table on view in data view. Then it must put the measure somewhere so that it can be used. Then somehow I need to pull these separate measures together somehow so that I can create a metric in my dashboard (say a gauge) such that I can add it to my Milestone slicer in the dashboard. OR do the measures all get calculated together in a single statement and put into a PP generated pivot table ie maybe I do not need to use DAX at all - I don't know.
To an expert this is bread and butter, to someone new to it - that's why I'm here asking dumb questions. Once I've been through the pain barrier, I'll be ok but it's not easy getting there.
CALCULATE(COUNT(BusinessChange[AnyColumnWithoutBlanks],BusinessChange[Status]="Complete") - now with this additional piece of information, I have a working measure (but had to change COUNT to COUNTA to get it to work). Also, I now know that I need to repeat this measure for each Dimension table and I need to apply a filter to the DAX to only select the current month - ie the latest data. Oh yes and it has put the result somewhere - as an fx field at the end of the available fields in the Milestone table. It remains a mystery as to why it has put it there, but I'll assume for now that I don't need to worry where it lives.
Thanks for your help. I'll let you know how I get on combining the measures into a single headline metric.
Dear friend,
as mentioned in my first post, without seeing a sample file is hard to visualize your structure.
It can be done in 5 minutes, but not without a sample file that will answer other questions like why "the 1 end going into the Milestone table"?.
The dimension table is supposed to have unique keys, the fact table can have multiple keys.
We cannot recreate your data based on your description. It is your task to provide a sample file that will save time and will provide more details than you can write in 2000 words.
We will gladly help you, but we cannot spend hours recreating your data structure.
Thank you for understanding
My solution...
=CALCULATE(
COUNTA(
BusinessChange[Id]
),
BusinessChange[Status] = "Complete",
FILTER(
BusinessChange[IsCurrentMonth] = "Yes"
)
)
where [IsCurrentMonth] is a DAX column that calculates whether the Date is in the current month or not to filter out the latest transactions...
= IF(
YEAR ( BusinessChange[Data] = YEAR ( TODAY () )
&& MONTH ( BusinessChange[Date] = MONTH ( TODAY () ),
"Yes",
"No"
)
Now to calculate similar for the other dozen or so Fact tables.
Yes, you are right it is my Dimension table that is in the middle of the Star with the 1 attached. The dozen or so Fact tables are the many end of the relationships. I'm used to calling them Reference tables (Dimensions) and Transactional tables (Facts) from a data modelling background.
Yes, you are right it is my Dimension table that is in the middle of the Star with the 1 attached. The dozen or so Fact tables are the many end of the relationships.
Glad to hear you managed to make it work.
Now you see how confusing a description without a file can be 🙂
