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
Anonymous
Not applicable

Relationship issue with Measure

Hi Experts

I have been looking at this problem for 3 days and cannot see why my table to the right does not show the same p-value for each of the month periods. 

Scenario

If you select product id 72201092  which has a p-value of 0.013345 when you look at the table to the right for each period (date) the p-vlaue os 1.0000 when it should be the same for each data point. 

 

Sample file. https://www.dropbox.com/s/jj62h2vxtepm5yd/Sample__Test%281%29.pbix?dl=0

 

it seems that there is no relationship between meausre and month field.

1 ACCEPTED SOLUTION

 

New TableNew Table

My Table = 
VAR OuterComplaints = COUNTROWS ( PMS_COMPLAINT )
RETURN
ADDCOLUMNS (
    VALUES ( PMS_FINANCIAL_PDS[Month Start] ),
    "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
    "Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE ),
    "Outer Complaints", OuterComplaints
)

Something along these lines...

 

View solution in original post

13 REPLIES 13
freder1ck
Kudo Kingpin
Kudo Kingpin

Hi @Anonymous,

Nice dashboard and some advanced (to me) stats! I've tagged this with #statistics to get the attention of the stats geniuses. 

 

When I click each month bar below, I notice that the p-value is 1. Wouldn't this be correct? That is, the correlation between month 10 and the dataset of month 10 only would be one. 

 

Your data model and relationships look pretty good. I have a couple of suggestions. 

  1. Your calendar table is monthly not daily so it can't be marked as a date table. Be sure to turn off auto date time for this report in settings (Options and Settings: options: Current File: Time Intelligence: Auto date/time). Doing this will reduce your file size.
  2. You have multiple transaction tables/fact tables connected to a shared lookup or dimension table with bi-directional filtering, which can create ambiguity. For complaint description, it's 1-1 but you can actually set it as 1-many or many-1 (put the many on the complaint table side). Not sure why you set Parts to bi-directional. If it's to cross-filter slicers, then there's a better way

 

organized data modelorganized data model

Anonymous
Not applicable

here is a more up to date file, replacing the older file from yesterday.

 

https://www.dropbox.com/s/w4p1z5ueibwrwxd/Sample__Test.%281%29.pbix?dl=0

Anonymous
Not applicable

I cannot see the file - would you please create a new link to the file?

Anonymous
Not applicable

Anonymous
Not applicable

based on your excellent feedback, if you take from PMS_TM1_Sales_vol Table column (month_date) and add that to the table it give you the right p-value but errors on the inidivdual data points shows 846 as a constant number for 12 periods.

 

As opposed to the actual value (see image  below) - fixed one problem another one comes along.Capture.PNG

 

Haven't been able to look at this a whole lot the past couple of days. The problem of a wrong value in a total is generally the result of evaluating a measure with no filters. The fix is either to do a SUMX of the measure over the distinct months, or to have an IF statement that evaluates the measure differently if there are no filters.

Anonymous
Not applicable

Another expert also tried

I've just tried to see what values EARLIER formula returns, and it seems like it returns the same value as if there were no EARLIER function.

 

I created the measure below to check the output:

__testS = 
VAR Mytable1 = ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]);"CountComplaints";CALCULATE(COUNTROWS(PMS_COMPLAINT));"Index";RANKX(PMS_FINANCIAL_PDS;PMS_FINANCIAL_PDS[Month Start];;;Dense))
VAR cc = 
SUMX(Mytable1; 
    SUMX(Mytable1;
        EARLIER([CountComplaints])
    )
)
VAR i = 
SUMX(Mytable1; 
    SUMX(Mytable1;
        EARLIER([Index])
    )
)
RETURN
    i & ", " & cc

Instead of EARLIER, you could set variables to fix values outside the SUMX. You could also preview the results of your MyTable by doing a new table from the modeling tab. You could also use ADDCOLUMNS to preview the amounts at the row level.
Anonymous
Not applicable

hi freder1ck

 

could you kindly demo this table - i follow what you are saying but slighly confused at the start. i'll happily add a demo file if you need one. thanks once again.

 

New TableNew Table

My Table = 
VAR OuterComplaints = COUNTROWS ( PMS_COMPLAINT )
RETURN
ADDCOLUMNS (
    VALUES ( PMS_FINANCIAL_PDS[Month Start] ),
    "CountComplaints", CALCULATE ( COUNTROWS ( PMS_COMPLAINT ) ),
    "Index", RANKX ( PMS_FINANCIAL_PDS, PMS_FINANCIAL_PDS[Month Start],,, DENSE ),
    "Outer Complaints", OuterComplaints
)

Something along these lines...

 

Anonymous
Not applicable

hi Freder1ck

firstly many thanks, i believe the answer lies with the following measure.

 

__s = VAR Mytable1 = ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]),"CountComplaints",CALCULATE(COUNTROWS(PMS_COMPLAINT)),"Index",RANKX(PMS_FINANCIAL_PDS,PMS_FINANCIAL_PDS[Month Start],,,Dense))
RETURN
SUMX(Mytable1, 
    SUMX(Mytable1,
        IF([Index]>EARLIER([Index])&&[CountComplaints]<EARLIER([CountComplaints]), 1,
        IF([Index]>EARLIER([Index])&&[CountComplaints]>EARLIER([CountComplaints]), -1))
    )
)
Anonymous
Not applicable

Hi Expert

When I click each month bar below, I notice that the p-value is 1. Wouldn't this be correct? That is, the correlation between month 10 and the dataset of month 10 only would be one. - How could we make this change this correlation from 1 to 1 to 1 to 12 oranother number...

Anonymous
Not applicable

Hi Freder!ck

 

When I click each month bar below, I notice that the p-value is 1. Wouldn't this be correct? - NO should be the value in the table for each data point

That is, the correlation between month 10 and the dataset of month 10 only would be one.  - As above

 

Your data model and relationships look pretty good. I have a couple of suggestions. 

  1. Your calendar table is monthly not daily so it can't be marked as a date table. Be sure to turn off auto date time for this report in settings (Options and Settings: options: Current File: Time Intelligence: Auto date/time). Doing this will reduce your file size. - Done may thanks
  2. You have multiple transaction tables/fact tables connected to a shared lookup or dimension table with bi-directional filtering, which can create ambiguity. For complaint description, it's 1-1 but you can actually set it as 1-many or many-1 (put the many on the complaint table side). Not sure why you set Parts to bi-directional. If it's to cross-filter slicers, then there's a better way.  - You are correct on the bi-direction realtion ship

one quick question how do you tag a question to #statistics for future reference

 

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.