Forum Discussion

newgirl's avatar
newgirl
Post Patron
3 years ago
Solved

Advanced filtering

Hello   I have a sample data model with my current project like below. Data tables are: Activity table - records activities made by salespersons Creation table - records when a client was create...
Need Help
  • BrianConnelly's avatar
    BrianConnelly
    3 years ago

    Need to update the code as such using variables...

    Volume_related = 
VAR startDate = MIN('Calendar'[Date])
VAR endDate = MAX('Calendar'[Date])
RETURN CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn])
,InvoiceDate >= startDate && InvoiceDate <= endDate
)
amitchandak's avatar
amitchandak
Super User
3 years ago

newgirl , FOr such a case you have use merge queries in power query  and get creation date of client in sales 

 

Do not expand take the min/max of the date (Aggregation)

Merge Tables (Power Query) : https://www.youtube.com/watch?v=zNrmbagO0Oo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=16

 

Or create a new column in DAX in Sales

Minx(filter('Creation Date', 'Creation Date'[Client ID] = sales[Client ID]) ,'Creation Date'[Creation Date] )

 

Then you can join with sales creation date with date, if join is inactive you can use userelationship  in measure

example

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Employees as on Date : https://youtu.be/e6Y-l_JtCq4

newgirl's avatar
newgirl
Post Patron
3 years ago

Hello amitchandak !

 

I tried what you suggested in my actual working file:

 

This is the relationship:

 

And this is the new measure (Vol_Cust) 

Volume_related = CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn]))

 

I tried to filter how the measure got 45. I understand how it works but...

How can I filter it further to get 22.4? The logic I want to apply is that since the date slicer in the page is filtered to Feb 1 to Feb 28, the measure should calculate for the sum of the volume filtered to the 'CreatedOn' date and the 'Invoice Date'. Hope you can help me how the measure can be improved.

  • BrianConnelly's avatar
    BrianConnelly
    Resolver III
    3 years ago

    Need to update the code as such using variables...

    Volume_related = 
VAR startDate = MIN('Calendar'[Date])
VAR endDate = MAX('Calendar'[Date])
RETURN CALCULATE([Volume],USERELATIONSHIP('Calendar'[Date],'CR tbl_phrawsales'[HisSapZ0123_GEOLOC.CreatedOn])
,InvoiceDate >= startDate && InvoiceDate <= endDate
)