cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DAX formula to calculate different calculation

Hi everyone, 
I am reaching out to experts and peers here to get an idea and hopefully a solution. My environment is SSAS Tabular 2016 Enterprise Ediiton, and my tabular model has two tables, for simplicity.  Power BI and Excel will be our front end visualisation tool. In the future, we will be looking at using AAS/Premium.

  1. Fact: 
    • Injury Count
    • Reporting Date Key
  2. Dates (Date dim)
    1. Date Key
    2. Day,
    3. Week
    4. Month Name
    5. Year
    6. Month Indexer (to sort Month)

 My business users will report Injury Counts for day, and week. For daily counts they want a SUM of all counts. But for weekly counts, they want the count on the last day of the week. That's because, the weekly count represents how many injuries were there at the end of the week showing the measures taken. The report should also enable drill down from Week to Day. 


I am looking at following options

1 - Have two different tables in my model. Week and Date. Both of them will have Date keys so  I should be able to drill through from Week to Date using Date (Date Ke).

2 - Keep one table with different measures for Day and Week calcs. I have about 18 measures right now, and they are expected grow to about 50+. Maintenance wil be a bit of pain. Downside of this approach, perhaps, is that there won't be any drilldown or drillthrough, I haven't tried it. 

3 - Write DAX measure to handle day and week in the same measure. for daily calculation, do a SUM and for weekly calc, pick up the last day of week's number. I have no idea how will I write it, at this stage :).

 

I am attaching a sample dataset along with sample output on the first page. 

Data and output sample 
Thanks
Kaz 

 

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: DAX formula to calculate different calculation

The file is unreachable. You need to place it on a shored drive that anyone can access. Sharepoint is a bad idea. Please use OneDrive or Google Drive.

Best
D
Highlighted
Anonymous
Not applicable

Re: DAX formula to calculate different calculation

// All columns in the fact table must be hidden.
// Only measures are allowed in fact tables.
// Slicing and dicing is done only through dimensions.
// Keys are always hidden and helper fields as well.
// There is a 1:* from the Dates table to the FactTable
// and filtering is one-way only.
// The Dates table must be marked THE date table in the model
// and the field holding a date is Day. I understand that DateKey is
// an integer.

[Injury Counts] =
var __oneDayVisible = HASONEVALUE( Dates[DateKey] )
var __oneWeekVisible =
	HASONEVALUE( Dates[Week] )
	&& COUNTROWS( Dates ) = 7
var __sumOfCounts = sum( FactTable[InjuryCount] )
var __countForLastDay =
	CALCULATE(
		sum( FactTable[InjuryCount] ),
		LASTDATE( Dates[Day] )
	)
return
	switch( true(),
		__oneDayVisible, __sumOfCounts,
		__oneWeekVisible, __countForLastDay	
	)

Best

D

Highlighted
Helper V
Helper V

Re: DAX formula to calculate different calculation

It is actually OneDrive. I have unshared and shared it again. Please use this link.

Highlighted
Helper V
Helper V

Re: DAX formula to calculate different calculation

@Anonymous  thanks so much for writing that out. I'll give it a try.

 

All but two of your pre-reqs are not matched. the date table can't be marked as the date table of model as I have got multiple dates in there. and, the relationship betwen date and fact table is one to many. 

As a side note, I have kept a base date table coming from my data mart, and and have created calculated tables based of the base date table. And, my calculated tables have two hierarchies, financial and calendar period that go from year through to day/date.

Highlighted
Anonymous
Not applicable

Re: DAX formula to calculate different calculation

@krajani, you have to have a date table in the model. No sound model can do without it.

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors