Forum Discussion

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago
Solved

Cross Table formulas not working

Hi! Having some challenges again getting some dax basic formulas to do some proper calculations   The 3 tables relevant to this are : 1) All CIs 2) Config Metrics - Updated. 3) Data table (this...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Lenihan,

     

    Since you have the relationship between these tables, you can calculate based on date table.

     

    	% Updated =COUNTA('Config Metrics - Updated'[CI Identifier]) /CALCULATE(COUNTA('All CIs'[CI Identifier]),RELATED(Date[report Month]))

     

     

    in addition, you can also use current reporting month to calcualte.

     

    	% Updated =
	var currMonth= max('Config Metrics - Updated'[Reporting Month])
	return
	COUNTAX(FILTER(ALL('Config Metrics - Updated'),'Config Metrics - Updated'[Reporting Month]=currMonth),[CI Identifier])
	/
	COUNTAX(FILTER(ALL('All CIs'),'All CIs'[Reporting Month]=currMonth),[CI Identifier])

     

     

    If above is not help can you share some sample file?

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Lenihan,

 

Since you have the relationship between these tables, you can calculate based on date table.

 

	% Updated =COUNTA('Config Metrics - Updated'[CI Identifier]) /CALCULATE(COUNTA('All CIs'[CI Identifier]),RELATED(Date[report Month]))

 

 

in addition, you can also use current reporting month to calcualte.

 

	% Updated =
	var currMonth= max('Config Metrics - Updated'[Reporting Month])
	return
	COUNTAX(FILTER(ALL('Config Metrics - Updated'),'Config Metrics - Updated'[Reporting Month]=currMonth),[CI Identifier])
	/
	COUNTAX(FILTER(ALL('All CIs'),'All CIs'[Reporting Month]=currMonth),[CI Identifier])

 

 

If above is not help can you share some sample file?

 

Regards,

Xiaoxin Sheng

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago

Thank you Anonymous   The second function worked (first I had some errors with the ,RELATED section, but first worked great, and gave all the correct values. Thank you!

 

 

When i read your formula, it's like "Of course!".. but.. i know I never would have gotten to that point. This may help me with other measures I have to create too.