Forum Discussion
summarizing based on different tables
Hi!
I've been breaking my head about this, I inherited someone else's PBIX file and I'm trying to make a management summary.
The file has 4 tables:
1 Accountname (has a 1 to many relationship with the other 3)
2 subject1
3 subject2
4 subject3
Table 2,3 & 4 all have a column containing accountname on which the relationship is based.
Now I have created a healthindicator in table 2, 3 & 4, that is either a calculated column or a measure that gives back a numerical value to be used as a black (4),Red(3),amber(2),green(1) (BRAG) status if for one of the user there is a problem for that subject.
For example:
For subject 1 there are 3 different measurements, and if measurement one is green, measurement 2 is red, and measurement 3 is red, the indicator for subject1 would give the value 3 for red.
What I want to achieve is to (distinct) count the total amount of users that have one or more problems in any of the subject tables.
I tried creating a new measure in the Accountname table, but I can't seem to get it to work.
Can someone either pseudocode or point me to the relevant documentation that would help me here?
Edit:
I was trying something like this:
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
2 Replies
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