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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yggdrasill
Responsive Resident
Responsive Resident

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 ? 

Capture.PNG

 

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 = ??? 




1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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)

 

create1.jpg

 

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_])))

 

create2.jpg

 

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_])

 

create3.jpg

 

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.

View solution in original post

7 REPLIES 7
v-zhenbw-msft
Community Support
Community Support

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)

 

create1.jpg

 

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_])))

 

create2.jpg

 

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_])

 

create3.jpg

 

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!


ToddChitt
Super User
Super User

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.

Can you use FILTER statements, possibly next the CALCULATETABLE statements?

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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