Forum Discussion
Create a table with DAX using filters on subquery
Greetings! Been struggling a bit with this what I thought was going to be simple task but I guess not....
How can I change this table on the left to the table on the right ?
Note that I'm excluding the X marked area because that document does not contain a revenue account!
What I've tried so far:
I created a new table to get all Documents with one or more revenue account listed with a simple logic because all revenue accounts start with "1".
Documents =
CALCULATETABLE (
DISTINCT ( Table1[Document No_] ),
LEFT ( Table1[Account No], 1 ) = "1"
)
Then I need to create a table only where both of these criterias are met
1) Documents where revenue is listed in one or more rows
2) Exclude revenue accounts and only list non-revenue accounts
OtherTable = ???
Hi Yggdrasill ,
We can create two columns in your left table and create a new calculate table to meet your requirement.
1. Create a calculate column to judge the revenue.
judge_account = var _left = LEFT('Table'[Account No_],1) return IF( _left="1",1,0)2. Create another column to get the sum of judge_account column.
same_document = CALCULATE(SUM('Table'[judge_account]),FILTER('Table','Table'[Document No_]=EARLIER('Table'[Document No_])))3. Create a new table to get the result.
Table 2 = SUMMARIZE( FILTER( 'Table','Table'[judge_account]=0 && 'Table'[same_document]>0), 'Table'[Account No_],'Table'[Account Name],'Table'[Document No_])If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
7 Replies
Personally, I would do it in Power Query.
Duplicate the base table (if you still need it as such) and apply filters and transform steps as needed until you get to the data you want.
ToddChitt
Hi and thanks for the reply.
That means I will query the base table twice, inside PQ, rather than loading the base table once and DAX query on top of that. Correct me if I'm wrong because this is also a question of performance cause the base table contains > 10million rows.Yggdrasill OK, fair enough. What about REFERENCE a query in PQ? To be honest, I have never checked if the source database gets hit twice if you simply reference another query instead of DUPLICATE.
Hi Yggdrasill ,
We can create two columns in your left table and create a new calculate table to meet your requirement.
1. Create a calculate column to judge the revenue.
judge_account = var _left = LEFT('Table'[Account No_],1) return IF( _left="1",1,0)2. Create another column to get the sum of judge_account column.
same_document = CALCULATE(SUM('Table'[judge_account]),FILTER('Table','Table'[Document No_]=EARLIER('Table'[Document No_])))3. Create a new table to get the result.
Table 2 = SUMMARIZE( FILTER( 'Table','Table'[judge_account]=0 && 'Table'[same_document]>0), 'Table'[Account No_],'Table'[Account Name],'Table'[Document No_])If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi Yggdrasill ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the simple solution v-zhenbw-msft . Looking at it now I can't believe I didn't figure this out!
Cheers!
