Understanding DAX Query

[Anonymous]

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

[v-piga-msft]

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

[Anonymous]

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 · Accepted solution]

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