Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 relations...
  • Anonymous's avatar
    Anonymous
    7 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