Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculated Tables Advice

Earlier Opening Opportunity Mon-Fri Summary Table = 
FILTER (
    SUMMARIZECOLUMNS (
        'Earlier Opening Opportunity Mon-Fri Detail Table'[ShopKey],
        "Net Sales Ex Vat", (SUM ( 'Earlier Opening Opportunity Mon-Fri Detail Table'[Net Sales Ex VAT] ))/5),
    [Net Sales Ex Vat] > 40)​
Earlier Opening Opportunity Mon-Fri Detail Table = 
FILTER( 
    FILTER(
        SUMMARIZECOLUMNS (
            DimShop[ShopKey],
            DimDate[WeekOfYear],
            DimDate[DateKey],
            DimDate[DayNameShort],
            CurrentWeekTimes[Opening Time],
            DimTime[TimeSlot],
            "Net Sales Ex VAT", SUM ( FactSalesDetail[Net Sales Ex VAT] )),
        DimDate[DayNameShort] in {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}),
    DimTime[TimeSlot] = CurrentWeekTimes[Opening Time])

 

 

Hi All

 

I am creating a Calculated Table from my Main Fact Table (2nd Code Snippet) and then creating a second summary Calculated Table (1st code Snippet) in order to use the ShoKey in the first Table to get a unique count of records.

 

Can anyone give me any pointers on a potentially easier way to do this, either one Calculated Table or perhaps a single measure to get the Count i require?

 

Thanks in advance!

 

DAX

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'm pretty confident that there's a cleaner way but, working with what I see, I'd suggest exploring a solution along these lines:

    Earlier Opening Opportunity Mon-Fri Count =
VAR Summary =
    FILTER (
        SUMMARIZECOLUMNS (
            DimShop[ShopKey],
            CurrentWeekTimes[Opening Time],
            DimTime[TimeSlot],
            TREATAS (
                { "Monday", "Tuesday", "Wednesday", "Thursday", "Friday" },
                DimDate[DayNameShort]
            ),
            "@NetSalesExVAT", CALCULATE ( SUM ( FactSalesDetail[Net Sales Ex VAT] ) )
        ),
        DimTime[TimeSlot] = CurrentWeekTimes[Opening Time]
    )
VAR Grouped =
    GROUPBY (
        Summary,
        DimShop[ShopKey],
        "@ShopSales", SUMX ( CURRENTGROUP (), [@NetSalesExVAT] )
    )
RETURN
    COUNTROWS ( FILTER ( Grouped, ( [@ShopSales] / 5 ) > 40 ) )
  • m3tr01d's avatar
    m3tr01d
    Continued Contributor
    4 years ago

    Anonymous 
    The questions you need to ask yourself is do you really need to do a Calculated table for your usecase?
    The problem is we don't know what you want to do.

    90% of the time, you don't need to build a Calculated Table and 95% of the time, you don't even need to know the function SummarizeColumns / Summarize.

    - Explain to us the different tables of your model and the relationship

    - Give us an example of the final output you want to have (maybe with an excel table)

    Then, we can discuss about the possibilities