Count Distinct Values Over Time & slice

[MatWebb]

Hi There,

Hopefully someone can help with this:

My dataset is for a logistics company and has one main fact table (shipments) and many dimensions tables (including, activities, places, countries, cargotype etc). 

Shipments have a 'load' date and a 'discharge date' which is the start and end of the shipment. 

Im trying to establish how many vehicles are 'Active' at anyone time.

Each shipment is associated with an activity. There is an activity table and some of those activities are 'active' activities (like enroute) and some are 'inactive' activities (like repairs). These status's are marked in the Activities table.
Each vehicle has a unique id (imo) and can make more than 1 shipment in a time period.

I would like to show the number of unique active vehicles over a period of time. say monthly.

The logic i would like to produce is, for june 2017, for example:

if LoadDate <= June 30th 2017 AND DischargeDate >= June 1st 2017 then count the 'unique' vehicles in that period

I would then like to be able to slice this data and show the number of Active vs Inactive vehicles that make up all vehicles from the period.

my problem

> i can get the answer by using a filter on the shipments table, but that involes using an all() that then prevents me slicing the result. (see commented out section in the code below)

> another version using a filter on the datetable gets close to the right answer, but not quite right - but double counts some vehicles that made an 'active' and an 'inactive' shipment in the period. As long as there is 1 'active' status from a vehicle in the period, all 'inactive' statuses should be ignored.

My code snippet:

m_CountDistinctIMO_OverTime = CALCULATE(
				[m_Count_IMONumber],
				
				//ERROR - using ALL() on the main fact table means slicers dont work on it.
			        //notes: vessels in transit durin CURRENT month and dispatched
				//notes: load <=end of current month AND dispatch >=start of current month				
			        /*FILTER(	
					all(Shipments),
					Shipments[LoadDate] < max(DateTable[lastDayOfMonth])+1 &&
					Shipments[DischargeDate] >= min(DateTable[firstDayOfMonth])
				)*/
				
				//ERROR - data is not correct and is not distinct.	
				FILTER (
					ALL ( DateTable ),
					
					DateTable[lastDayOfMonth] > min(Shipments[LoadDate])
					&& DateTable[firstDayOfMonth] <= MAX(Shipments[DischargeDate])
								
				)
		)

And a demo file: https://drive.google.com/file/d/11E3g002oSPKfN9bz4n772xe_7e7Hsyce/view?usp=sharing

Hopefully someone can help?

Thanks in advance!

Mat

[v-yulgu-msft]

Hi @MatWebb,

---

 > i can get the answer by using a filter on the shipments table, but that involes using an all() that then prevents me slicing the result. (see commented out section in the code below)

---

I was not very clear about this problem, because in my test, the active and inactive values are both changed dynamically when I change the slicer selections in your report file. Could you please illustrate this problem with an example?

Also, you said using an all() is incorrect, please try to replace it with ALLSELECTED() to see whether it works.

m_CountDistinctIMO_OverTime = CALCULATE(
				[m_Count_IMONumber],
				FILTER (
					ALLSELECTED ( DateTable ),
					DateTable[lastDayOfMonth] > min(Shipments[LoadDate])
					&& DateTable[firstDayOfMonth] <= MAX(Shipments[DischargeDate])
								
				)
		)

Regards,

Yuliana Gu

[MatWebb]

Hi @v-yulgu-msft thanks for helping,

Appologies if it wasnt clear - I had commented out the dax in the measure (in the code example) for the one that returns the right result but is not sliceable. Here it is, uncommented so you can see what i am trying to do:

m_CountDistinctIMO_OverTime = CALCULATE(
				[m_Count_IMONumber],
				
				//ERROR - using ALL() on the main fact table means slicers dont work on it.
			        //notes: vessels in transit durin CURRENT month and dispatched
				//notes: load <=end of current month AND dispatch >=start of current month				
			        FILTER(	
					all(Shipments),
					Shipments[LoadDate] < max(DateTable[lastDayOfMonth])+1 &&
					Shipments[DischargeDate] >= min(DateTable[firstDayOfMonth])
				)
)

I have amended the measure in the demo file too so you can see it not slicing. I have just tried using Allselected() as you suggested and although it makes the visuals slica-able again, the incorrect result is returned. As a guide - the correct result for June 2017 is 2691.

Here's the new version fo the demo file: https://drive.google.com/file/d/1bPUXMFMsNBRUhXmyU6pNvWzv1oMH7u6p/view?usp=sharing

If there is anything else i can do to better illustrae the problem to enable you to assist then please just say and ill do all i can.

many thanks again for your help

Mat

[MatWebb]

Anyone got any ideas on this one? It's driving me up the wall!

appreciate any ideas or *prods* in the right direction

Cheers

Mat

[MatWebb · Accepted solution]

This is now solved - through lots of trial and error i managed to get there using allselected() and then adding my dimentions tables as filters in the calculate.