Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lenihan
Helper III
Helper III

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 is a calculated table created with a unique key created following these instructions - https://pbidax.wordpress.com/2016/06/03/connect-any-number-of-tables-together-via-a-common-column/

 

Every table (except Data table) has a column called "Reporting Month". I have January 2017 and February 2017 as the values. 

 

I need to calculate the % of CIs updated in the month. So, take # updated per month, and divide by total # of CIs that month. 

 

First is: 

UpdatedTotal = counta('Config Metrics - Updated'[CI Identifier])    

This is Total count of CIs updated in the month. This will be the numerator.

 

Second is:

TotalCIs = counta('All CIs'[CI Identifier])

This is total # of CIs available. This will be the denominator

Both of these give correct values. 

 

This third one:

% Updated = [UpdatedTotal] / [TotalCIs]   

 

Is not giving me a value that even makes sense once i add the Reporting Month on the Axis. I've tried adding the column Reporting Month from the "All CIs" table, and from the "Config Metrics - Updated".

When I select the Reporting month column from "All CIs" table, it only shows a value for January (and it is incorrect)

When I select the Reporting month column from "Config Metrics - Updated", it says Infinity.

 

I'm unsure what to do next so that it is correct.

 

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you @v-shex-msft   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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.