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

DAX: Not in Summuraized List

I have invoices that hit multiple account groups ( Employee Related and Other). However I want to only count it once. For example, if Employee Related and Other have both invoice 1234, I only want it to count towards the Employee Related group.
 
I am trying to calulate the # of invoices that are in Other that are not in Employee Related. Below is the code, but I am getting an error because it cannot have a list. Also I am uncertain how to create a InvoiceList of the Invoice Nbr that is related to Employee Related.
 
# of Other Invoices = 

VAR InvoiceList = SUMMARIZE('FDW Transactions','FDW Transactions'[Invoice Nbr])

RETURN

CALCULATE([# of Invoices], 'Mapping XC'[CAT_GROUP_1_NM] IN {"OTHER"}, NOT('FDW Transactions'[Invoice Nbr] IN{InvoiceList}))
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX: Not in Summuraized List

Hi @dh731r 

Create a table

Table3 = VALUES(Table2[CAT_GROUP_1_NM])

Don't create any relationship for these tables, (or just many to many relationship for table 1 and table 2)

any of the following is ok

Capture14.JPGCapture13.JPG

 

2.Change the "XC Code" in Table 2 with "Code" as a new column name

Capture15.JPG

Then Create a measure in Table3

Measure 2 =
IF (
    MAX ( 'Table3'[CAT_GROUP_1_NM] ) = "Other",
    COUNTROWS (
        EXCEPT (
            SUMMARIZE (
                FILTER (
                    CROSSJOIN ( Table1, Table2 ),
                    Table1[XC Code] = Table2[Code]
                        && Table2[CAT_GROUP_1_NM] = "Other"
                ),
                [Invoice Number]
            ),
            SUMMARIZE (
                FILTER (
                    CROSSJOIN ( Table1, Table2 ),
                    Table1[XC Code] = Table2[Code]
                        && Table2[CAT_GROUP_1_NM] = "Employee Related"
                ),
                [Invoice Number]
            )
        )
    ),
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                CROSSJOIN ( Table1, Table2 ),
                Table1[XC Code] = Table2[Code]
                    && Table2[CAT_GROUP_1_NM] = "Employee Related"
            ),
            [Invoice Number]
        )
    )
)

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: DAX: Not in Summuraized List

Hi @dh731r 

Could you share a simple example?

Relationship between tables :'Mapping XC' and  "FDW Transactions"?

[# of Invoices] is a measure?

Employee Related and Other are two columns or different values in a column?

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dh731r Frequent Visitor
Frequent Visitor

Re: DAX: Not in Summuraized List

Hi @v-juanli-msft 

 

Yes  [# of Invoice] is a measure.

 

Here is the relationships with the left 2 columns being the FDW Transactions and the right 2 columns being Mapping XC. They are separate tables in my dataset, but showing it in one table here for simplicity.

 

Invoice NumberXC Code XC CodeCAT_GROUP_1_NM
150E 50EEmployee Related
251E 51EEmployee Related
3213 213Other
4411 411Other
5222 222Other
6613 613Other
1222 222Other
2411 411Other

 

What I want to capture is:

Employee Related # of Invoices = 2

Other # of Invoices = 4

 

4 because 1 and 2 reside under the main one which is Employee Related.

 

Please let me know if this makes sense.

Community Support Team
Community Support Team

Re: DAX: Not in Summuraized List

Hi @dh731r 

Create a table

Table3 = VALUES(Table2[CAT_GROUP_1_NM])

Don't create any relationship for these tables, (or just many to many relationship for table 1 and table 2)

any of the following is ok

Capture14.JPGCapture13.JPG

 

2.Change the "XC Code" in Table 2 with "Code" as a new column name

Capture15.JPG

Then Create a measure in Table3

Measure 2 =
IF (
    MAX ( 'Table3'[CAT_GROUP_1_NM] ) = "Other",
    COUNTROWS (
        EXCEPT (
            SUMMARIZE (
                FILTER (
                    CROSSJOIN ( Table1, Table2 ),
                    Table1[XC Code] = Table2[Code]
                        && Table2[CAT_GROUP_1_NM] = "Other"
                ),
                [Invoice Number]
            ),
            SUMMARIZE (
                FILTER (
                    CROSSJOIN ( Table1, Table2 ),
                    Table1[XC Code] = Table2[Code]
                        && Table2[CAT_GROUP_1_NM] = "Employee Related"
                ),
                [Invoice Number]
            )
        )
    ),
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                CROSSJOIN ( Table1, Table2 ),
                Table1[XC Code] = Table2[Code]
                    && Table2[CAT_GROUP_1_NM] = "Employee Related"
            ),
            [Invoice Number]
        )
    )
)

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and 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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 281 members 3,096 guests
Please welcome our newest community members: