Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
1. I need to create a measure as a filter that can choose the period time between columns "date start" and "date end" of table "date dim of lead (2 independent filter orders to choose 2 different time periods)
2. I need to compare the results from the filters.
I provide 2 pictures, one is the model relationship, and one is my concept to filter and compare the results that the 2 filters pointed out.
I've searched and followed similar topics before (time intelligence), but I haven't been able to do it.
Please support me and request me if you have any questions about my description of my problem.
Solved! Go to Solution.
I would venture that you need to duplicate the date table to use in the comparison measure. Link the duplicate date table to the fact table in an inactive relationship and use the function USERELATIONSHIP in the comparison measure and TREATAS to link to the date values in the visuals
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown,
The algorithm has a problem when I try to choose the start time towards the middle of the date comp slicer. The total doesn't seem to work very well even though it shows full details of the total amount per week but is not counted.
Can you check it again for me? Here is a picture I took and red-circled some of my doubts.
To get the correct totals, use the following measure:
Comp Date lead total =
SUMX ( VALUES ( 'Axis Table'[WeekNum] ), [Comp date lead] )
I've attached the edited file
Proud to be a Super User!
Paul on Linkedin.
Yep, thank you so much!
It worked very well. I can rely on your file to do the same with other metrics in my dashboard,
but I have a question, is the USERELATIONSHIP function needed in my dashboard? Do you have any idea for me?
No, you don't need the USERELATIONSHIP function for the calculation you are looking for. You would use it to activate the relationship between the date and the end date, but as it is, the start/end dates are periods referring to weeks, so the end date is redundant.
Proud to be a Super User!
Paul on Linkedin.
Your advice is super useful for me. Thank you very much! I very appreciate it! @PaulDBrown
So, Let me clarify once again my intentions on the sample data sheet above. I want to create a dashboard that can track and analyze over time my phase of lead and cost-related metrics.
My concept is to create a time filter between the start time and end time; one more is a function that can duplicate the newly created filter to compare columns on the same value but different intervals (intervals I can choose based on filter).
I have consulted many topics on the forum, but none are really similar to my case. It would be greatly appreciated to easily get specific instructions to get used to DAX on the Power BI.
See if this works for you:
I've attached the sample file for you:
Proud to be a Super User!
Paul on Linkedin.
I would venture that you need to duplicate the date table to use in the comparison measure. Link the duplicate date table to the fact table in an inactive relationship and use the function USERELATIONSHIP in the comparison measure and TREATAS to link to the date values in the visuals
Proud to be a Super User!
Paul on Linkedin.
May I give you some data examples, you could help me with the solution in particular. I'm stuck in the middle of not knowing which implementation (time comparison, comparing 2 objecting, filter between date start and date end columns) to implement to achieve my intention.
https://drive.google.com/file/d/1xXFu7JsyU2xHuiDW0ARRSdIb0TULXJWV/view?usp=sharing
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |