cancel
Showing results for 
Search instead for 
Did you mean: 

Data getting filtered differently on Power Pivot and M Query

Hi,

The table structure is as attached in the snapshot.

 

Problem that i m facing is, some date values do not match the entries in the US_2 table when the date table is designed from Power Pivot. there is no such issue when Date Table is designed from M Query editor.

(I used the same file to check this scenario)

 

* I do not see error on date column in US_2

* Date range in the calendar table designed from Power Pivot a/ Power Query is the same

* Data type and format verified in both US_2 and DIM_DateTable and are found to be consistent

However, I see that there are some date values (in US_2) that do not match with date values in calendar table (when the table is built using Power Pivot). There is no such problem when I use M Query editor.

 

Can anyone suggest what could be the issue?

Date range for Calendar table is picked up from helper table which hold relative start and end dates

 

Structure.PNG

 

Data of Usage Statistics

 

c    11-02-201711-03-201711-06-201711-07-201711-08-201711/14/201711/15/201711/16/201711/17/201711/20/201711/21/201711/22/201711/25/201711/27/201711/28/201711/29/201712-01-201712-04-201712-05-201712-06-201712-07-201712-08-201712-11-201712-12-201712/13/201712/14/201712/15/201712/18/201712/19/201712/20/201712/21/201712/28/201701-02-201801-03-201801-04-201801-05-201801-08-201801-09-201801-10-201801-11-201801-12-20181/15/20181/16/20181/17/20181/18/20181/19/20181/22/2018
0Portal Report NameQuery DescriptionUser NameFunctionTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal timeTotal time
1Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  ACM522 22    22   22  22   22     22   222  222        11  22  
2Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  BCM 55               333  33 333     33   333 333   33   22  3311
3Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  CCM  555  22 44  44   44   4444   44 44 44 44       44 44    44  
4Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  DCollection Team   11                            44  44       55   
5Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  ECollection Team    11 55  55   55   55    55   55 22 33  5522 55 11 55 111  55 
6Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  FAtr    11                                          
7Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  GCM     22 22            33  22                       
8Top N to be Collected PRMS                                  Top N to be Collected PRMS                                  HCM  33 33    44   44 33  22                            
9Top N to be Collected PRMS Per CLSP                         Top N to be Collected PRMS Per CLSP                         ICM                                               
 

 

US_1 comprises of Row_Id, Username, Function Name and Report name

US_2 comprises of Row_Id, date columns (Unpivot)

 

Date column from DIM_DateTable connects to Date field (Attribute) in US_2

There exists relationship between US_1 and US_2 on Row ID field.

 

Correct Snapshot:

expected_Correct.PNG

 

Error Snapshot

Error_snapshots.PNG

 

 

 

 

Status: Needs Info
Comments
Moderator

Hi @mannu,

 

Would you please share a pbix file to clarify the issue? 

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Needs Info