CODEWITHSUNDEEP
sql-serverpowerpivotwindow-functionsdax
sliber
sliber

Reputation: 11

SQL analytical function to DAX

Although it might sound like I want to add window function to powerpivot table, but I'm not... I want to exclude records based on windowing function.

I have such a query similar to this (SQL Server):

Select  Line_Number, 
        Suite_name, 
        Group_Name, 
        Revision, 
        Status 
FROM (
    select  Line_Number, 
            Suite_Name, 
            Group_Name, 
            Revision, 
            Status, 
            Row_Number() OVER (Partition by Line_Number, Suite_Name, Group_Name order by Revision Desc) r
    From TableA 
    ) t
where t.r = 1

This query returns the highest Revision's row per Line_Number, Suite_Name, and Group_Name.

I want to do the same, but with DAX in Excel Power Pivot.

The result will be count of Line Numbers, but for the selected (slicers) Suite and Group.

The goal is to select all rows into the tabular data model, and then, using similar functionality, filter the records and keep only latest revision per applied slicers.

I tried so far: CALCULATE, FILTER, TOPN, RANKX, GENERATE. None of them helped me to dynamically "exclude" rows from the calculation.

Can anyone suggest an approach?

Thanks!

Upvotes: 0

Views: 721

Answers (2)

sliber
sliber

Reputation: 11

It appears that I did things right, but while exploring solutions for this I realized that sometimes cells reference does not do the work, so I had to work in 3 steps. Furthermore, in order to reduce complexity in my question, I didn't mention that I also have many to many tables. Some code examples:

Last rev:=CALCULATE(MAX(REV), 'TABLE B', 'TABLE C')

Now when I first tried to do things, this is how the code looked like:

Total Lines:=CALCULATE(COUNTROWS(TABLE A), 'TABLE B', 'TABLE C', FILTER(ALLSELECTED(TABLE A[REV]), [REV] = Last Rev))

Unfortunately this did not work, so I had to update the code to this:

Total Lines:=CALCULATE(COUNT('TABLE A'[REV]), 'TABLE B', 'TABLE C', FILTER(ALL('TABLE A'[REV]), [REV] = CALCULATE(MAX(REV), 'TABLE B', 'TABLE C')))

Only then the filter that I was after really did the job. After getting this done, I still had to deal correctly with summarize, so I had wrap the Total Lines measure with another sum expression, otherwise I will get only "1": Total:=SUMX(VALUES('Table A'[LINE_NUMBER]),[Total] )

More info about 'Table B' and 'Table C': these are many to many tables, 'Table B' is the intermediate table and 'Table C' is the dimension table itself

Upvotes: 0

WimV
WimV

Reputation: 1005

A suggestion could be to use a calculated column to find the max revision value, using the earlier function. This will not work if you want to have a dynamic filter on a range of revisions.

calc_column01:=CALCULATE(max(myTable[revision]),filter(myTable,myTable[line_number]=EARLIER(myTable[line_number])&&myTable[suite_name]=earlier(myTable[suite_name])))

Then add a measure01:=CALCULATE(VALUES(myTable[status]),filter(ALLSELECTED(myTable),myTable[revision]=max(myTable[calc_column01])))

powerpivot window

sampledate and result in powerpivot:

result

Upvotes: 1

Related Questions