Forum Discussion
summarizing based on different tables
- Anonymous7 years ago
Anonymous I think you can try something like this:
Measure := VAR AllIssues = ADDCOLUMNS( VALUES(AccountName[AccountName Column]) ,"Sub1Measure1", [:Measurement1 BRAG] // CALCULATE(MAX(Subject1[Measurement 1 column])) ,"Sub1Measure2", [:Measurement2 BRAG] // CALCULATE(MAX(Subject1[Measurement 2 column])) ,"Sub2Measure1", [:Measurement1 BRAG] // CALCULATE(MAX(Subject2[Measurement 1 column])) //...etc. ) VAR AccountsWithIssues = FILTER( SummaryTable ,[Sub1Measure1] > 2 || [Sub1Measure2] > 2 || [Sub2Measure1] > 2 // || ...etc ) VAR Result = COUNTROWS(AccountsWithIssues) RETURN Result
The goal is the following:
You start with the unique list of AccountNames using VALUES(AccountName_Table[AccountName column])
Use ADDCOLUMNS to create a table with the unique list of Account names, and generate the result of each of the BRAG measures for each individual account. For BRAG calculations that use MEASURES, just reference the measure. For BRAG calculations that use CALCULATED COLUMNS, use
CALCULATE( MAX( TableName[BRAG Column Name] ) )
You need the CALCULATE() in there to force context transition. That will evaluate the MAX() function for only the current Account Name.
Once you generate your table with all of these values, you FILTER() that table down to only the Account names that have at least one of those BRAG columns > 2.
COUNTROWS() will then show you how many accountnames survive that FILTER() criteria.
Hope this helps,
~ Chris H
Anonymous I think you can try something like this:
Measure := VAR AllIssues = ADDCOLUMNS( VALUES(AccountName[AccountName Column]) ,"Sub1Measure1", [:Measurement1 BRAG] // CALCULATE(MAX(Subject1[Measurement 1 column])) ,"Sub1Measure2", [:Measurement2 BRAG] // CALCULATE(MAX(Subject1[Measurement 2 column])) ,"Sub2Measure1", [:Measurement1 BRAG] // CALCULATE(MAX(Subject2[Measurement 1 column])) //...etc. ) VAR AccountsWithIssues = FILTER( SummaryTable ,[Sub1Measure1] > 2 || [Sub1Measure2] > 2 || [Sub2Measure1] > 2 // || ...etc ) VAR Result = COUNTROWS(AccountsWithIssues) RETURN Result
The goal is the following:
You start with the unique list of AccountNames using VALUES(AccountName_Table[AccountName column])
Use ADDCOLUMNS to create a table with the unique list of Account names, and generate the result of each of the BRAG measures for each individual account. For BRAG calculations that use MEASURES, just reference the measure. For BRAG calculations that use CALCULATED COLUMNS, use
CALCULATE( MAX( TableName[BRAG Column Name] ) )
You need the CALCULATE() in there to force context transition. That will evaluate the MAX() function for only the current Account Name.
Once you generate your table with all of these values, you FILTER() that table down to only the Account names that have at least one of those BRAG columns > 2.
COUNTROWS() will then show you how many accountnames survive that FILTER() criteria.
Hope this helps,
~ Chris H
That did the trick, and was exactly what I was looking for. Thank you!