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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

sum by mainItem

Hi, 

What I thought would be an easy tasked proved more complex then I thought

 

Table A

itemSKcount
a5
b5
c6
d6
a7
b6
b1
d3

 

Item table

ItemSKMainitemid
ao
bo
cp
dp

 

Result

ItemCount by mainitem
a24
b24
c15
d15

 

 

Help me write dax for "Count by mainitem" please 🙂

 

I could give you all my filed attempts but i don't think it will help(?)

 

regards

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @Anonymous 

See the attached PBIX.

 

First, you should ensure the model is set up like this (which I imagine it already is):

OwenAuger_0-1685689954823.png

 

Then create this measure:

Count by mainitem = 
CALCULATE (
    SUM ( 'Table A'[count] ),
    REMOVEFILTERS ( 'Item' ),
    VALUES ( 'Item'[Mainitemid] )
)

This measure sums 'Table A'[count] for the values of 'Item'[Mainitemid] corresponding to the visible rows of 'Item', but removing any other filters on the 'Item' table.

 

OwenAuger_1-1685690032769.png

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @OwenAuger

 

Thanks for your quick reply. 
I am getting an error on REMOVEFILTERS, 


"Cannot find name 'REMOVEFILTERS'
'REMOVEFILTERS' is not a function."

 

I am using direct query to access data and there is a connection between the tabels like you describe (My model is more complex then this but I scaled down to only highlight the importnat parts. 

I replaced REMOVEFILTER with ALL and from what i can se this is giving me the deciered result. 

Thanks

Glad to hear it's working (using ALL rather than REMOVEFILTERS).

These functions are indeed equivalent when used in this way.

 

If REMOVEFILTERS is unavailable, I'm guessing you are connecting to an older version of Analysis Services Tabular.

 

For reference, the compatibility for DAX functions can be found on dax.guide:

https://dax.guide/removefilters/

OwenAuger_0-1685695002117.png

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @Anonymous 

See the attached PBIX.

 

First, you should ensure the model is set up like this (which I imagine it already is):

OwenAuger_0-1685689954823.png

 

Then create this measure:

Count by mainitem = 
CALCULATE (
    SUM ( 'Table A'[count] ),
    REMOVEFILTERS ( 'Item' ),
    VALUES ( 'Item'[Mainitemid] )
)

This measure sums 'Table A'[count] for the values of 'Item'[Mainitemid] corresponding to the visible rows of 'Item', but removing any other filters on the 'Item' table.

 

OwenAuger_1-1685690032769.png

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors