Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I have a date table and a case table(which store the client information, when they come, when they active, when they close etc)
I have slicer, which is the month from the date table. For example, when I click Nov, I want to know how many client is still active on Nov 1(the begining of the slicer month).
The criteria should be (activation date before the begining of the select month and (the closed date is null or the closed date after the begining of the month))
My main relationship between these two tables are assigned to date = date table. So when to create relatioship using activation date or closed date, it needs to use userrelationship.
Below is my case table:
Solved! Go to Solution.
Try to create a measure like this:
selected people =
CALCULATE(
COUNTX(
FILTER('Table','Table'[activation date]<=MIN('Date'[Date])
&&'Table'[assigned date]<=MIN('Date'[Date])
&& (ISBLANK('Table'[closed date])
|| 'Table'[closed date]>MIN('Date'[Date]))),
('Table'[Person Name])),
CROSSFILTER('Table'[assigned date],'Date'[Date],None)
)
Dear @xiumi_hou ,
I have attached a solution pbix
YOu just need to make a date table and add a month column to it .
And then relate date table's date column to activation date column of your raw data table
thats it !!
Happy to help you
If this solves your problem please let me know with accepting this post as solution and giving a like to this post
and remember to help others on community too
Regards
Thakur Sujit
Hi Thanks for the reply. My relationship is assigned date with date table. I can only write to query to complete this. I have some other modle within this pdix file. Can you please helpo me take a look? For example, when I selecet May, I want all the people whose activation date happem before May 1st and those people whose closed date after May 1st(or do not have a closed date). In summary, number of people who was active at May 1st. Thanks
Try to create a measure like this:
selected people =
CALCULATE(
COUNTX(
FILTER('Table','Table'[activation date]<=MIN('Date'[Date])
&&'Table'[assigned date]<=MIN('Date'[Date])
&& (ISBLANK('Table'[closed date])
|| 'Table'[closed date]>MIN('Date'[Date]))),
('Table'[Person Name])),
CROSSFILTER('Table'[assigned date],'Date'[Date],None)
)
UP
UP
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |