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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PoweredOut
Resolver I
Resolver I

Allexcept is working, but doesn't work in Matrix

I have a Matrix table with coulmns from my DIM Account table and columns from my Fact table. I want to add multiple measures to the matrix. My measures work as expected, but one of them doesn't. 

 

I have a measure time intelligence measure that is shows me the sales from last year. I want the measure to ignore all other columns in the matrix except for the Customer Name. I currently have this measure which works when I remove the columns from the matrix, but appears as blank when the columns are added.  Any ideas?

 

Total Bookings - One Year Ago = CALCULATE([Amount Booked YTD] , DATEADD('DIM Date'[Date], -1, YEAR), ALLEXCEPT('DIM Account', 'DIM Account'[CustomerName], 'DIM Account'[CompanyParent]))

 

1 ACCEPTED SOLUTION
PoweredOut
Resolver I
Resolver I

I manged to get it to work by adding the Customer ID column from the fact table to the Matrix and using the following code . 

CALCULATE([Amount Booked YTD],DATEADD('DIM Date'[Date], -1, YEAR), ALLEXCEPT('Deals', 'Deals'[CompanyID], 'DIM Date'[Date], 'DIM Account'[CompanyParent], 'DIM Account'[CustomerName]))

 

View solution in original post

3 REPLIES 3
PoweredOut
Resolver I
Resolver I

I manged to get it to work by adding the Customer ID column from the fact table to the Matrix and using the following code . 

CALCULATE([Amount Booked YTD],DATEADD('DIM Date'[Date], -1, YEAR), ALLEXCEPT('Deals', 'Deals'[CompanyID], 'DIM Date'[Date], 'DIM Account'[CompanyParent], 'DIM Account'[CustomerName]))

 

amitchandak
Super User
Super User

@PoweredOut , Ideally, last year should work with

 

CALCULATE([Amount Booked YTD] , DATEADD('DIM Date'[Date], -1, YEAR))

 

Make sure date table has all dates and is marked as date table

 

 

or try like

 

Total Bookings - One Year Ago = CALCULATE(CALCULATE([Amount Booked YTD] ALLEXCEPT('DIM Account', 'DIM Account'[CustomerName], 'DIM Account'[CompanyParent])), DATEADD('DIM Date'[Date], -1, YEAR))

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thank you @amitchandak the measure works, but when it's in the Matrix, it still appears as blank 

PoweredOut_0-1676459953826.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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