topic Re: Calculated Tables Advice in DAX Commands and Tips

Calculated Tables Advice

Anonymous — Tue, 30 Nov 2021 16:05:11 GMT

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!

Re: Calculated Tables Advice

AlexisOlson — Tue, 30 Nov 2021 18:00:10 GMT

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 ) )

Re: Calculated Tables Advice

m3tr01d — Wed, 01 Dec 2021 00:32:51 GMT

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