Measure for Status of 'Complete' over multiple workbooks|Power Pivot|Excel Forum|My Online Training Hub

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.

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.

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.

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.