ALL function not working when more filters are used in the page

[Anonymous]

Hi, 

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

Imagine we have 2 tables  [MainStarTable] and [DM_AgeGrouping] and both are connected via PK as a many to one relatioship. 

Here are the below DAX im using in a table widget without sorting any data in it. Each of the below DAX is used as a column inside the table as shown below.

DAX1 -   AgeGroup= CALCULATE(sum(DM_AgeGrouping[validRecordbit]))

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

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

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 DAX is returing the expected results. But when I use all the filters combined DAX3 and DAX2 is not working as expected

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

[Anonymous]

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

Imagine we have 2 tables  [MainStarTable] and [DM_AgeGrouping] and both are connected via PK as a many to one relatioship. 

Here are the below DAX im using in a table widget without sorting any data in it. Each of the below DAX is used as a column inside the table as shown below.

DAX1 -   AgeGroup= CALCULATE(sum(DM_AgeGrouping[validRecordbit]))

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

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

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 DAX is returing the expected results. But when I use all the filters combined DAX3 and DAX2 is not working as expected

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

[amitchandak]

@Anonymous , What is issue you are facing in term of values ?

This

ALL(MainStarTable)

ALL(MainStarTable[FID])

Should work. In the second case if you will view by FID, you see only selected value with all total unfiltered value

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

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

[Anonymous]

@amitchandak Thanks for the prompt response. The issue is I'm missing the records in the Nagegroup values.

Please find attached test file where I create a mock data to reflect the scenario.  There is no option to attach a file here so I have uploaded this to DropBox.

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

In simple I need the ouput as 

But when I select a combination of all the filters I'm getting the below output instead of getting the above one.