Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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