Star Schema and Power BI

[Anonymous]

Hello Experts, 

I have a Star Schema similar to mentioned here on the Microsoft page. I am trying to build a DAX query that has Role playing dimensions which acts as a filter as well in this case. However, these are the things I would like to achieve here:

1. Format the Time to hh:mm in query
2. Kind of Pivot the measures
3. The Purchase and Delivery Dates should be set to the dates I pass if the original dates fall in that range. I should also be able to pass these dates dynamically instead of hard coding.
4. If I pass more than a week range dates, the measures (day of transaction) should only be included as one (Eg: 1,23 instead of 1,1,2,2,3,3).

Please let me know if you need any other information to help me.

Thanks!

[Anonymous]

There was some limitation with the number of characters in the original message. So, posting further information here:

Here is some sample data how it looks like:
'EZ09A', 'NW', '1/1/2020', '1/4/2020',  'Jumanji Steelers', '1/1/2020 10:00:00 AM', '1/4/2020 02:00:00 PM', 3
'B9A34', 'SE', '1/2/2020', '1/6/2020',  'Jumanji Steelers', '1/2/2020 06:30:00 AM', '1/6/2020 10:00:00 AM', 4
'EZ09A', 'NW', '1/2/2020', '1/6/2020',  'Jumanji Steelers', '1/2/2020 10:00:00 AM', '1/6/2020 02:00:00 PM', 4
'Y57P0', 'SW', '1/3/2020', '1/7/2020',  'Jumanji Steelers', '1/3/2020 10:30:00 AM', '1/7/2020 04:30:00 PM', 5
'EZ09A', 'NW', '1/3/2020', '1/7/2020',  'Jumanji Steelers', '1/3/2020 10:00:00 AM', '1/7/2020 02:00:00 PM', 5

 
Example-1:
Here is what I tried so far:

EVALUATE SUMMARIZECOLUMNS( 'Dim_Product'[ProductCode], 'Dim_SalesTerritory'[TerritoryName], 'Dim_Date_Purchase'[FullDate], 'Dim_Date_Delivery'[FullDate], 'Dim_Reseller'[ResellerName], 'Dim_Time_Purchase'[FullTime], 'Dim_Time_Delivery'[FullTime], FILTER('Dim_Date_Purchase', 'Dim_Date_Purchase'[FullDate] >= DATE(2020, 01, 01) ), FILTER('Dim_Date_Delivery', 'Dim_Date_Delivery'[FullDate] <= DATE(2020, 01, 05) ), "Days of Transactions", 'Fact_Purchase_Transactions'[Days of Transactions] ) 

 
Here is what I am expecting as output:
'EZ09A', 'NW', '1/1/2020', '1/4/2020', 'Jumanji Steelers', '10:00', '14:00', '**345**'
'B9A34', 'SE', '1/2/2020', '1/5/2020', 'Jumanji Steelers', '06:30', '10:00', '***4***'
'Y57P0', 'SW', '1/3/2020', '1/5/2020', 'Jumanji Steelers', '10:30', '04:30', '****5**'
 
Example-2:
Here is what I tried so far:

EVALUATE SUMMARIZECOLUMNS( 'Dim_Product'[ProductCode], 'Dim_SalesTerritory'[TerritoryName], 'Dim_Date_Purchase'[FullDate], 'Dim_Date_Delivery'[FullDate], 'Dim_Reseller'[ResellerName], 'Dim_Time_Purchase'[FullTime], 'Dim_Time_Delivery'[FullTime], FILTER('Dim_Date_Purchase', 'Dim_Date_Purchase'[FullDate] >= DATE(2020, 01, 04) ), FILTER('Dim_Date_Delivery', 'Dim_Date_Delivery'[FullDate] <= DATE(2020, 01, 05) ), "Days of Transactions", 'Fact_Purchase_Transactions'[Days of Transactions] ) 

 Here is what I am expecting as output:
'B9A34', 'SE', '1/4/2020', '1/5/2020', 'Jumanji Steelers', '06:30', '10:00', '***4***'
'Y57P0', 'SW', '1/4/2020', '1/5/2020', 'Jumanji Steelers', '10:30', '04:30', '****5**'

[Anonymous]

The first example can be written like this (I have not tried it out, though):

EVALUATE

// These two variables can get values from outside
// but how to do it depends on what's calling this
// query. If it's called from SSRS, for instance,
// I think you can pass values using
// @DateStart, @DateEnd like this:
// 		var __purchaseDateStart = @DateStart
// 		var __purchaseDateEnd = @DateEnd
// But the specific mechanism is dependent on the
// technology you use.

// By they way, why are you making the names of the
// tables so long and thus not easily readable? It's
// really more than enough to say 'Product' instead
// of Dim_Product. Same goes for all the other tables,
// especially the fact table monster.

var __purchaseDateStart = DATE ( 2020, 01, 01 )
var __purchaseDateEnd = DATE ( 2020, 01, 05 )
return
CALCULATETABLE(
	SUMMARIZECOLUMNS (
	
	    'Dim_Product'[ProductCode],
	    'Dim_SalesTerritory'[TerritoryName],
	    'Dim_Date_Purchase'[FullDate],
	    'Dim_Date_Delivery'[FullDate],
	    'Dim_Reseller'[ResellerName],
	    'Dim_Time_Purchase'[FullTime],
	    'Dim_Time_Delivery'[FullTime],
	    
	    "Days of Transactions",
	    	var __daysOfTran =
	    		VALUES( 'Fact_Purchase_Transactions'[Days of Transactions] )
			var __valuesForString =
				ADDCOLUMNS(
					GENERATESERIES(1, 7),
					"DisplayValue",
						var __number = [Value]
						if(
							__number in __daysOfTran,
							__number,
							"*"
						)
				)
			return
	    	CONCATENATEX(
	    		__valuesForString,
	    		[DisplayValue],
	    		"",
	    		[Value],
	    		ASC
	    	)
	),
	
	'Dim_Date_Purchase'[FullDate] >= __purchaseDateStart,
	'Dim_Date_Delivery'[FullDate] <= __purchaseDateEnd
)

The other one can be obtained similarly.

You might need to adjust GENERATESERIES(1, 7) but from what you've presented I gather that the numbers can be from 1 to 7.

Best

D

[Anonymous]

By the way, if you want to format times you can:

1. Either do SELECTCOLUMNS on the result above with some FORMAT( [Datetime Field], "HH:mm" )

or

2. Add additional calculated columns under SUMMARIZECOLUMNS using FORMAT and then do SELECTCOLUMNS on the result.

Best
D