VAR Virtual Table Measure to Filter Fact Table based on DAX rather than creating Calculated Columns

[aggiebrown]

Hi Experts,

I have complex enterprise model, which contains Pipeline Data Stamps from various marketing events, such as:

- Event Date

- Total_created_events

- Total_owner_assigned_events

- Total_contact_events etc...

All of those are stored in Fact table which looks something like this (Dummy File Attached below)

https://1drv.ms/u/s!AoIOEd5cDuqQuWq9i9buJynDiUy1?e=iD3azU 

I am trying to build different views in the report which would show different context and that's when I am getting stuck. I have to use DAX Measures as opposed to Calculated Columns due to the size of dataset.

I have calculated all Leads that belong to particular owner, but when I am putting it on Monthly Graph it double counts the Lead which was originally in his name in January, then moved to someone else and then was put back in his name in February. (lead_4)

I only want to see it once, based on Latest Assign Date or if not, to be able to see it but mark it as "Recycled" or something on the graph to be able to differientate. 

So I am currently getting this wrong (lead_4 is double counting where it should only show in February and not January as well):

I also want to be able to show this view (all of the leads that belong/ed to John) but by Lead Created Date, rather than when he became Owner. Currently showing as Blank because he did not Own the Lead when it was created and that is the filter on the page ( I need to be able to filter only to particular Owners on this report, just not sure what is the best way to do it). 

Any help is much much appreciated . Thank you. 

[lbendlin]

your "dim_pipeline_event" table is not really a dimension. It should be refactored into a proper dimension (unique list of leads ) and the rest of the data should move into the fact table.

You can create multiple relationships between a dimension table and a fact table. For example Created Date and Close Date, or Created By and Closed By.  Then you use USERELATIONSHIP to temporarily activate relationships in your measures.

While you can use virtual tables as part of the measure calculation the final result of the measure MUST be a scalar value.