Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
_Regina
Helper I
Helper I

ALL function not behaving as expected

Hi everyone,

 

I have the following measure. What I am trying to do here is get the number of distinct 'onboarding ids' where there is a transaction.

 

Onboarding ID is in a fact table named 'Onbaording requests', transactions are in a fact table name 'Sales transactions'. Both these tables have a common column 'ACCOUNT_DIM_SYSID'.

 

Both the fact tables have relation with 'Date' table.

'Onbaording requests'[start date] to Date[Date]

'Sales transactions'[transaction dtae] to to Date[Date]

 

I have used NATURALINNERJOIN to obtain the result. The only thing is that I don't want the date slicer to affect Sales transactions, it should only affect 'Onboarding requests' table. I have a date slicer on my report page which is based on Dtae table.

 

For ex:

If I choose June 2023, It should give me all 'onboarding id' that have start date in June 2023 and have an associated transaction, even if the transaction occured outside of June 2023. 

 

I have used ALL function in my variable b., but it still giving me all onboarding id where start date is in June 2023 as well as Transaction Dtae is in June 2023.

 

_test =
VAR a =
       SELECTCOLUMNS (
                'Onboarding Requests',
                "ACCOUNT_DIM_SYSID", 'Onboarding Requests'[ACCOUNT_SYSID] + 0,
                "onboarding_id", 'Onboarding Requests'[ONBOARDING_ID]
         )
VAR b =
      SELECTCOLUMNS (
             FILTER (
                    ALL ( 'Sales Transactions'[ACCOUNT_DIM_SYSID] ),
                   [_Product Net Sales Buy Sell Transaction Count] > 0
             ),
            "ACCOUNT_DIM_SYSID", 'Sales Transactions'[ACCOUNT_DIM_SYSID] + 0
       )
VAR R =
             NATURALINNERJOIN ( a, b )
VAR result =
             COUNTROWS ( SUMMARIZE ( R, [onboarding_id] ) )
RETURN
           result

 

Any guidance here is appreciated. @amitchandak @AlexisOlson 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

The measure [_Product Net Sales Buy Sell Transaction Count] still has the date filter context active.

 

Maybe removing the date filtering in the definition of b will help.

VAR b =
    SELECTCOLUMNS (
        CALCULATETABLE (
            FILTER (
                ALL ( 'Sales Transactions'[ACCOUNT_DIM_SYSID] ),
                [_Product Net Sales Buy Sell Transaction Count] > 0
            ),
            ALL ( 'Date' )
        ),
        "ACCOUNT_DIM_SYSID", 'Sales Transactions'[ACCOUNT_DIM_SYSID] + 0
    )

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

The measure [_Product Net Sales Buy Sell Transaction Count] still has the date filter context active.

 

Maybe removing the date filtering in the definition of b will help.

VAR b =
    SELECTCOLUMNS (
        CALCULATETABLE (
            FILTER (
                ALL ( 'Sales Transactions'[ACCOUNT_DIM_SYSID] ),
                [_Product Net Sales Buy Sell Transaction Count] > 0
            ),
            ALL ( 'Date' )
        ),
        "ACCOUNT_DIM_SYSID", 'Sales Transactions'[ACCOUNT_DIM_SYSID] + 0
    )

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.