cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nontmn Frequent Visitor
Frequent Visitor

Dax filter column in column

Hello,

I would like to Sum sale by filter other like this

CALCULATE(SUM(ZGI[sale]),FILTER(ZGI,(ZGI[model])=ZGI[model]))
I would like model from that row will filter other out and sum only this model.
but (ZGI[model])=ZGI[model] not working in column 
PS. I have to use in column not measure because I will use this column next.
Thank you
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dax filter column in column

Hi @nontmn ,

Based on your original formula, I modified it to calculte the values. 

 

Column 4 = DIVIDE(CALCULATE(SUM('Table'[A]),FILTER('Table',EARLIER('Table'[model])='Table'[model])),'Table'[A])

 

3.PNG

Is this what you want? If not, can you please post the expected result? Then we will understand your requirement clearly.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

9 REPLIES 9
Super User
Super User

Re: Dax filter column in column

@nontmn 

 

Try following

 

=
CALCULATE (
    SUM ( ZGI[sale] ),
    FILTER ( ZGI, ( ZGI[model] ) = EARLIER ( ZGI[model] ) )
)
nontmn Frequent Visitor
Frequent Visitor

Re: Dax filter column in column

Thank you It's working but when I use in another column for sum another thing it show 

"a circular dependency was detected" 

I have to make fomular like this for 3 column

Please help

Thanks

Super User
Super User

Re: Dax filter column in column

Hi @nontmn 

 

What formula did you use?

If you could copy paste some sample data and expected results, it will help in finding an appropriate solution

 

As a sample see the below post

https://community.powerbi.com/t5/Desktop/join-two-tables-and-find-min-after-doing-group-by/m-p/76893...

nontmn Frequent Visitor
Frequent Visitor

Re: Dax filter column in column

I use 

column1 = CALCULATE (SUM ( ZGI[sale] ),FILTER ( ZGI, ( ZGI[model] ) = EARLIER ( ZGI[model] ) ))

and next I create column 2 like this

column2 = CALCULATE (SUM ( ZGI[GI] )

It show "a circular dependency was detected:ZGI[GI],ZGI[sale],ZGI[GI]" 

Super User
Super User

Re: Dax filter column in column

@nontmn 

 

I don't see any error in your formula

Could you share your file?

 

What are you trying to achieve with

column2 = CALCULATE (SUM ( ZGI[GI] )

if your Table rows are different then above formula and column2 = ZGI[GI]  would yield same results

nontmn Frequent Visitor
Frequent Visitor

Re: Dax filter column in column

https://1drv.ms/u/s!AqosNctB7VSarCPtoiEg786KxV_h?e=B3rWp9

I create new file to compare issue at "Column 4"

Super User
Super User

Re: Dax filter column in column

@nontmn 

 

I checked your file.
What is your desired output in Column 4?

 

nontmn Frequent Visitor
Frequent Visitor

Re: Dax filter column in column

Actually, I have many version for one model so sometime I cannot indicate version, it has to share to other version.

My idea is create column that already shared sales by month and model but when I create another column with same method it show circular dependency detected(sum column[A]).

I already use measure method to share version but I found something wrong (subtotal sum up) . So I think it better to share before next calculation by measure.

Community Support Team
Community Support Team

Re: Dax filter column in column

Hi @nontmn ,

Based on your original formula, I modified it to calculte the values. 

 

Column 4 = DIVIDE(CALCULATE(SUM('Table'[A]),FILTER('Table',EARLIER('Table'[model])='Table'[model])),'Table'[A])

 

3.PNG

Is this what you want? If not, can you please post the expected result? Then we will understand your requirement clearly.

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 43 members 999 guests
Please welcome our newest community members: