CODEWITHSUNDEEP
filterpowerbi
Helena Viljoen
Helena Viljoen

Reputation: 19

Using ALL Function and FILTER Function from two Different Tables in PowerBI

Am new to PowerBI and after some research I am not getting the answer I require.

I am creating my first report on PowerBI and am struggling with the following :

I have a few tables in my starschema and I have applied a filter on my slicer to only show me the data for this month , I want to exclude the filter on two of fields. These fields are created by using filter already. I have the field - OutstandingClaims = CALCULATE(DISTINCTCOUNT('PA ClaimsPBI'[Claim No]),FILTER( 'PA ClaimsPBI','PA ClaimsPBI'[Claim Status Grouped] = "Claim Outstanding" ))

I want this field to be excluded from the filter of only showing the data from this month - I created the filter with a dates table I created in PowerBI. The Filter is on - Dates[Date]

After research I found that I can achieve this by using the ALL Function, but I don't know how to incorporate this as two different tables are being used to filter namely , PA ClaimsPBI and Dates.

I have tried a few solution but keep on getting syntax errors : Unfilterted = CALCULATE(COUNT('PA ClaimsPBI'[Claim No]),FILTER(ALL('PA ClaimsPBI','PA ClaimsPBI'[Entry Date]),'PA ClaimsPBI'[Claim Status Grouped]="Claim Outstanding"))

Can someone please help with the syntax on how to achieve this?

Thanks!!

Upvotes: 0

Views: 1049

Answers (1)

Pavel Klammert
Pavel Klammert

Reputation: 315

if i get it right, you only need to do something like this:

Unfilterted = CALCULATE(COUNT('PA ClaimsPBI'[Claim No]),ALL(Dates[Date])))

Which basically says calculate count of the column and ignore the context comming from Dates[Date].

If you want to keep the ClaimsPBI'[Claim Status Grouped]="Claim Outstanding"), you could just paste it as another parameter to calculate after the All.

Upvotes: 0

Related Questions