Forum Discussion
Understanding DAX Query
Hi, I have following DAX query which used to return values but user is saying it stoppped working since few months. I am not able to understand the DAX here. Someone help me understand the DAX output.
Note: Fact Tenancy[TenancyEndDate] has is Inactive relationship between 'Tenancy Date' Dimention and Tenancy[TenancyStartDate] is active relationship.
Tenancies New:=CALCULATE(COUNTROWS(Tenancy), 'Tenancy Date', FILTER(ALL( Tenancy[TenancyEndDate]),ISBLANK(Tenancy[TenancyEndDate])),'Geo Group')
Sample Fact Data:
TenancyID TenancyStartDate_SID TenancyStartDate TenancyEndDate_SID TenancyEndDate Suburb_SID WeeklyRentAmount
5 20160611 2016-06-11 20180901 2018-09-01 3249 210
29 20180910 2018-09-10 20180901 2018-09-01 3249 360
53 20180627 2018-06-27 20180901 2018-09-01 3249 380
77 20170912 2017-09-12 20180101 2018-01-01 3249 400
101 20170224 2017-02-24 20180501 2018-05-01 3249 420
125 20180212 2018-02-12 20180901 2018-09-01 3249 450
149 20160129 2016-01-29 20180101 2018-01-01 -1 280
173 20111014 2011-10-14 20180101 2018-01-01 4110 123
197 19990409 1999-04-09 20171201 2017-12-01 4110 150
221 20101111 2010-11-11 20180901 2018-09-01 4110 171.5
Hi Anonymous ,
Old Formula: Tenancies New:=CALCULATE(COUNTROWS(Tenancy), 'Tenancy Date', FILTER(ALL( Tenancy[TenancyEndDate]),ISBLANK(Tenancy[TenancyEndDate])),'Geo Group')
New Formula: Tenancies New:=CALCULATE(COUNTROWS(Tenancy),'Tenancy Date', FILTER(ALL(Tenancy[TenancyEndDate]),(Tenancy[TenancyEndDate])<> BLANK()),'Geo Group')
Based on your two formulas, it seems that the result should be really different.For the old formula, you will calcualted the rows that the Tenancy[TenancyEndDate] is blank.
For the new formula, you will calculate the rows that the Tenancy[TenancyEndDate] is not blank. So you could get the result with this formula for your scenario.
Best Regards,
Cherry
3 Replies
v-piga-msftResident Rockstar
Hi Anonymous ,
By my test with your formula and sample data, it returns blank.
I have a little confused about the 'Geo Group'. I cannot see it in your sample data.
If it is convenient, could you share your data sample which could reproduce your scenario and your desired output so that I could have a test on it.
Best Regards,
Cherry
Hi,
Thanks for the reply. I am also getting blank as results, I want to understand if the formula is filtering Blank records.
I replaced current formula with following and I am getting results. Want to know if I am doing correct? I can share PBIX file if you need.
Old Formula: Tenancies New:=CALCULATE(COUNTROWS(Tenancy), 'Tenancy Date', FILTER(ALL( Tenancy[TenancyEndDate]),ISBLANK(Tenancy[TenancyEndDate])),'Geo Group')
New Formula: Tenancies New:=CALCULATE(COUNTROWS(Tenancy),'Tenancy Date', FILTER(ALL(Tenancy[TenancyEndDate]),(Tenancy[TenancyEndDate])<> BLANK()),'Geo Group')
- v-piga-msft
Hi Anonymous ,
Old Formula: Tenancies New:=CALCULATE(COUNTROWS(Tenancy), 'Tenancy Date', FILTER(ALL( Tenancy[TenancyEndDate]),ISBLANK(Tenancy[TenancyEndDate])),'Geo Group')
New Formula: Tenancies New:=CALCULATE(COUNTROWS(Tenancy),'Tenancy Date', FILTER(ALL(Tenancy[TenancyEndDate]),(Tenancy[TenancyEndDate])<> BLANK()),'Geo Group')
Based on your two formulas, it seems that the result should be really different.For the old formula, you will calcualted the rows that the Tenancy[TenancyEndDate] is blank.
For the new formula, you will calculate the rows that the Tenancy[TenancyEndDate] is not blank. So you could get the result with this formula for your scenario.
Best Regards,
Cherry