DAX: Not in Summuraized List

[Anonymous]

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}))

[v-juanli-msft]

Hi @Anonymous 

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.

[Anonymous]

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 Number	XC Code		XC Code	CAT_GROUP_1_NM
1	50E		50E	Employee Related
2	51E		51E	Employee Related
3	213		213	Other
4	411		411	Other
5	222		222	Other
6	613		613	Other
1	222		222	Other
2	411		411	Other

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.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

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

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

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.