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
Monica
Frequent Visitor

Dinamic Correlation

Hi!

 

I've been trying to do what said in this post http://community.powerbi.com/t5/Community-Blog/DAX-as-a-Language/ba-p/22120 with my own data, the problem is that I did all process and I have the correlation but I need that if I do some filter the correlation has to be re calculate, it's because for example I need to find the correlation in different cities. So I changed your code:

 

 

 

//First X

CorrelateX3 = 
VAR AverageX3= AVERAGEX(ALLSELECTED('Llamadas Une'),[Valor])
RETURN	([Valor]-AverageX3)

//Then Y

CorrelateY3 = 
VAR 
	AverageY3 = AVERAGEX(ALLSELECTED('TRPS Une Hogares'),[Trps P Mas 25 MA])
RETURN
	 ([Trps P Mas 25 MA]- AverageY3)

//Finally the corelation

Correlation2 = 
VAR SumCorrelateX3SumCorrelateX3= SUMX(ALLSELECTED('Llamadas Une'),[CorrelateX3]*[CorrelateX3])
VAR SumCorrelateX3SumCorrelateY3= SUMX(ALLSELECTED('Llamadas Une'),[CorrelateX3]*RELATED('TRPS Une Hogares'[CorrelateY3]))
VAR SumCorrelateY3CorrelateY3= SUMX(ALLSELECTED('Llamadas Une'),RELATED('TRPS Une Hogares'[CorrelateY3])*RELATED('TRPS Une Hogares'[CorrelateY3]))
RETURN	(
SumCorrelateX3SumCorrelateY3 / SQRT(SumCorrelateX3SumCorrelateX3*SumCorrelateY3CorrelateY3))

 

 

I calculated the x average to testing:

 

 

//no filter
AverageX = AVERAGEX(ALL('Llamadas Une'),[Valor])

//Filter, it is like AverageX3

Average lla = AVERAGEX(ALLSELECTED('Llamadas Une'),[Valor])

 

 

I found that the error is because the subtraction between [Valor]-AverageX3 it's not ok:

 

2016-03-30 17_27_26-Sin título - Power BI Desktop.png

 

As you can see here, the result in CorrelateX3 is equal to subtract Valor-AverageX and not Valor-AverageX3. I don't know why it's happend here, Colud you Help me with this issue? or do you have other idea to do a dinamic correlation?

 

Thanks

 

0 REPLIES 0

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.