topic Re: ALL function not working as expected for a DAX Formula in DAX Commands and Tips

ALL function not working as expected for a DAX Formula

Anonymous — Tue, 16 Mar 2021 00:11:33 GMT

I'm working on a PowerBI report and one of the DAX is not working as expected. I use various filters in the page and at a particular scenario the ALL function is not working as expected.

For example I use below filters

1. MainStarTable[OID]

2. MainStarTable[FID]

3. MainStarTable[LengtOFstay]

4. MainStarTable[DateRange]

When using only FID and OID filters all the below DAX is returing the expected results. But when I use all the filters combined DAX3 and DAX2 is not working as expected

DAX1 =ageGroup= CALCULATE(sum(DM_AgeGrouping[validRecordbit]))

DAX2 =NageGroup= CALCULATE(sum(DM_AgeGrouping[validRecordbit]),ALL(MainStarTable[OID]))

DAX3 =OageGroup=CALCULATE(sum(DM_AgeGrouping[validRecordbit]),ALL(MainStarTable[FID],MainStarTable[OID])

Problem is with DAX3 and DAX2. For some reason DAX3/DAX2 is not utilizing the function ALL(MainStarTable[FID],MainStarTable[OID])

If anyone have any suggestion, please let me know. Thanks

Re: ALL function not working as expected for a DAX Formula

amitchandak — Tue, 16 Mar 2021 04:24:35 GMT

Anonymous , these seems correct. Are facing value issue or some error

DAX2 =NageGroup= CALCULATE(sum(DM_AgeGrouping[validRecordbit]),ALL(MainStarTable[OID]))

Means other then OID view in table, you should see filter on OID is removed. For the OID group by/view you will see total value without filter with selected value.

refer

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: ALL function not working as expected for a DAX Formula

Anonymous — Tue, 16 Mar 2021 11:33:42 GMT

amitchandak Yes I know how this works. Problem is I'm missing some values in the OID columns which is not suppose to happen when OID filters are used. Again use the below link to download the sample file.

https://www.dropbox.com/s/s0xkou30i1g1keg/TestFile.pbix?dl=0