How would you collapse all of these measures to create a more succinct aggregation?

[gemcityzach]

Hello fine folks,

I love PowerBI and I'm learning a ton as I go and reading a  ton of books. But with any new tool/language (M, DAX), there's so much I don't know how to do effeciently. Can you help me find a more succinct way to write these measures?

I have four tables of data that are related to an end-to-end business process. The data is in four different SharePoint lists. A user will complete records in List 1, then List 2, etc. The data is only related in the sense that it's part of the same business process. There is no other commonality among it that is germaine to this question.

Because the data is in four different tables, I have been struggling to come up with a way to aggregate completed records, total records, by Group and overall percentage complete. So, what I did was create a TON of measures in a new table that broke each step of each table/process down.

I.e., List 1, total records, total complete records, total records by group(1-4), total complete records by group(1-4), and did that across all four Lists. So as you can imagine I have like 50 measures to compute all of this.

Is there a better and more succinct way to write less measures to perform the same function? Below is an example of the code for one Group that cuts across all four tables. I've also attached a visual so you can see how the data is being populated.

--Table1 (EXT) Example (shows ID, Group(1-4 possible), Status1, Status2, recordStatus, which is 0 unless both Status1 and Status2 are complete, and tblName, which is unique to each of the four tables (EXT, REP, CUST, OSR).

ID	Group_Name	Status1	Status2	recordsStatus	tblName
1	Group 1		Complete	0	EXT
2	Group 1	Complete	Complete	1	EXT
3	Group 1	Complete		0	EXT
4	Group 1	Complete	Complete	1	EXT
5	Group 2	Complete		0	EXT
6	Group 2			0	EXT
7	Group 2	Complete	Complete	1	EXT

Example of Total Table Calculations and Calculations from One of Four Groups

--Example of Total Table Calculations that pull data from all of the adjacent table measures
-----------------------------------------------------------------------------------------------

--Cust Completed Records
CALCULATE(COUNTROWS('event-customer_risk_acks'),'event-customer_risk_acks'[recordStatus]=1)+0

--Cust Total Records
COUNTROWS ( 'event-customer_risk_acks' )

--OSR Completed Records
CALCULATE(COUNTROWS('event-osr_acks'),'event-osr_acks'[recordStatus]=1)+0

--OSR Total Records
COUNTROWS('event-osr_acks')

--EXT Completed Records
CALCULATE(COUNTROWS('event-ext_risk_acks'),'event-ext_risk_acks'[recordStatus]=1)+0

--EXT Total Records
COUNTROWS('event-ext_risk_acks')

--Completed Records
[repTotalCompleteRecords]+[osrTotalCompleteRecords]+[extTotalCompleteRecords]+[custTotalCompleteRecords]

--Total Records
[repTotalCols]+[osrTotalCols]+[extTotalCols]+[custTotalCols]

--Overall Percentage Complete
DIVIDE([totalCompleteRecords],[totalRecords],0)


--Example of Analysis for One Group
--REP Completed Records
CALCULATE(
    COUNTROWS('event-rep_acks')
    ,'event-rep_acks'[Target Group] = "Group 1"
    ,'event-rep_acks'[recordStatus]=1
    ) +0

--REP Total Records
CALCULATE(COUNTROWS('event-rep_acks'),'event-rep_acks'[Target Group]="Group 1")+0

--REP Percentage Complete
DIVIDE([cm-repCompleteRecords],[cm-repTotalRecords],0)


-- OSR Completed Records
CALCULATE(
    COUNTROWS('event-osr_acks')
    ,'event-osr_acks'[Target Group] = "Group 1"
    ,'event-osr_acks'[recordStatus]=1
    ) +0

--EXT Completed Records
CALCULATE(
    COUNTROWS('event-ext_risk_acks')
    ,'event-ext_risk_acks'[Target Group]="Group 1"
    ,'event-ext_risk_acks'[recordStatus] = 1
)+0

-- OSR Total Records
CALCULATE(COUNTROWS('event-osr_acks'),'event-osr_acks'[Target Group]="Group 1")+0

--OSR Percentage Complete
DIVIDE([cm-osrCompleteRecords],[cm-osrTotalRecords],0)

--EXT Percentage Complete
DIVIDE([cm-extCompleteRecords],[cm-extTotalRecords],0)

-- EXT Total Recods
CALCULATE(
    COUNTROWS('event-ext_risk_acks')
    ,'event-ext_risk_acks'[Target Group]="Group 1"
)+0

--CUST Total Records
CALCULATE(
    COUNTROWS('event-customer_risk_acks')
    ,'event-customer_risk_acks'[Target Group]="Group 1"
)+0

DIVIDE([cm-osrCompleteRecords],[cm-osrTotalRecords],0)

--CUST Completed Records
CALCULATE(
    COUNTROWS('event-customer_risk_acks')
    ,'event-customer_risk_acks'[Target Group]="Group 1"
    ,'event-customer_risk_acks'[recordStatus] = 1
)+0

--CUST Total Records
CALCULATE(
    COUNTROWS('event-customer_risk_acks')
    ,'event-customer_risk_acks'[Operating Group]="Group 1"
)+0

--Cust Percentage Complete
DIVIDE([cm-custCompleteRecords],[cm-custCompleteRecords],0)

--Group 1 Overall Percentage Complete
([cm-custCompleteRecords]+[cm-osrCompleteRecords]+[cm-repCompleteRecords]+[cm-extCompleteRecords])/([cm-custTotalRecords]+[cm-osrTotalRecords]+[cm-repTotalRecords]+[cm-extTotalRecords])

[Greg_Deckler]

@gemcityzach Can you UNION your tables together? Table.Combine in Power Query or UNION in DAX.

[gemcityzach]

Hey there. I suppose that is possible. But won't having all of those null values mess up filtering and counting of rows?

I figured that since I already had a table that's holding the individual measures, that there would be a more elegant way to bring many of the individual measures BY GROUP together? I.e., is there a way to combine all of the measures for a single group together instead of having 10+ measures?