cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 802 guests
Please welcome our newest community members: