Forum Discussion

dannyh246's avatar
dannyh246
Regular Visitor
8 years ago

Multiple Grouping & Sorting With Slicer

I have a fact table called FactAccumulation, which contains appox 500million records.

I have a dimension table called dimZone with about 50 records. 

 

Both are defined as follows. 

 

 

[FactAccumulation]

 

[Zone Key] [int] 
[Event Id] [int]
[Year] [int]
[Loss Usd] [decimal]

[DimZone]

[Zone Key] [int] 

[Zone Name] [VARCHAR] 

 

 

I want to be able to pick from a drop down (slicer) any number of Zones. Once i have the Zones selected, i want to 

 

1. Group the fact table on Zone & Year. (and summing up the Loss usd)

2. With the dataset from step 1, for each Zone Year pick the MAX loss value.

3. From the datatset in step 2 (Zone, Year, Max loss values), i want to partition by Zone order by Loss value descending and have row number appended.

 

In SQL it's fairly easy...something like this..

 

SELECT ROW_NUMBER() OVER(PARTITION BY occ.ZoneId ORDER BY occ.NetLossUsd DESC) AS RowNumber
             ,occ.[Year]
             ,occ.[ZoneId]
             ,occ.[NetLossUsd]
FROM
(
           SELECT e.[ZoneId]
                       ,e.[Year]
                       ,MAX(e.[NetLossUsd]) AS NetLossOurShareUsd
           FROM
            (
                   SELECT a.[EventId]
                               ,a.[ZoneId]
                               ,a.[Year]
                              ,SUM(a.[NetLossUsd]) AS NetLossUsd
                   FROM FactAccumulation a
                   GROUP BY a.[EventId]
                                     ,a.[AccumulationPerilId]
                                     ,a.[Year]
            ) e
            GROUP BY e.[ZoneId]
                             ,e.[Year]
            ) occ

 

How would i do this in PowrBi / DAX?

 

Any help would be much appreciated!!

 

Thanks

 

 

 

 

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi dannyh246,

     

    Please check out the demo here. 

    1. Create a calculated table as MiddleTable.

    MiddleTable =
SUMMARIZE (
    'FactAccumulation',
    DimZone[Zone Key],
    FactAccumulation[Event ID],
    FactAccumulation[Year],
    "Total", SUM ( FactAccumulation[Loss USD] )
)

    2. Create a measure for row numbers.

    RowNumEqually =
RANKX (
    ALL ( 'MiddleTable'[Year] ),
    CALCULATE ( MAX ( 'MiddleTable'[Total] ) )
)

    3. Create a table visual. The choose calculation type Maximum for column "Total".

    Multiple_Grouping_Sorting_With_Slicer

     

     

    Best Regards,

    Dale