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.
Hi All,
My DateDim is joined with the Account table using Open Date. I'm trying to find how many accounts are open at each month, hence using the Account[Opend Date]< End of Month and Close Date is empty or > the End of Month, which is what the Filter function for.
I want to ignore the pre-existing relationship between the DateDim Date and Account[Open Date] by using the Crossfilter(none). But this is not working at all, the relationship is still in active and filtering all accounts open in the month.
Where did I do wrong and how should I chagne it?
#Open = var __EoM = ENDOFMONTH(DateDim[Date]) return CALCULATE( COUNT(Account[ID]) ,FILTER(Account ,Account[Open Date]<=__EoM &&or(Account[Date Closed]>__EoM,ISBLANK(Account[Date Closed])) ) ,CROSSFILTER(Account[Open Date],DateDim[Date],None) )
I was just in the same problem. For someone with this kind of issue, the solution is to use a calculate inside of another calculate. Here's a sample:
Hi @Anonymous
Please see the below code.
measure = VAR m = MAX(DateDim[yearMonthNo]) -- concatinated Year month RETURN CALCULATE( COUNTROWS(Account), FILTER( ALL(DateDim), 'DateDim[yearMonthNo] = m ) )
Hope this helps.
Mariusz
Thanks Mariusz for your help, this did not work in this case.
I still need to keep the Filter ( ) function to the Account table by the Open Date and Closed Date from the account table.
I just wish to find a way to inactive the pre-existing relationship: DateDim[Date] = Account[Open Date]
Thanks again.
Hi @Anonymous
Please see the below code.
measure = VAR m = MAX(DateDim[yearMonthNo]) -- concatinated Year month RETURN CALCULATE( COUNTROWS(Account), FILTER( ALL(DateDim), 'DateDim[yearMonthNo] = m ) )
Hope this helps.
Mariusz
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |