cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snifer
Post Patron
Post Patron

comparing column row

so I've 2 different table

both tables contains a column called invoice nr,( the tables contain more column such as date and so on) my goal is to create a measure (or something else that could do the trick) to filter those columns for the same  date period and see what are the difference between those 2 tables ( invoice that are in the first table not contained on second

1 ACCEPTED SOLUTION

@snifer

 

Try this revision

Please see revised file as well

 

CalculatedTable =
VAR temp =
    CALCULATETABLE (
        EXCEPT (
            VALUES ( InvoiceLineTESTv4[Invoice Nr] ),
            VALUES ( ExtractTESTv4[Invoice Nr] )
        ),
        INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) )
    )
RETURN
    GENERATE (
        temp,
        CALCULATETABLE (
            SELECTCOLUMNS (
                InvoiceLineTESTv4,
                "Date", [Date],
                "Other Column1", [Other Column.1]
            )
        )
    )
Regards,
Zubair


View solution in original post

19 REPLIES 19
snifer
Post Patron
Post Patron

so I've 2 different table

both tables contain a column called invoice nr,( the tables contain more column such as date and so on) my goal is to create a table to filter those columns for the same  date period and see what are the difference between those 2 tables in the column amount -> if the amount is different for the same invoice, I need to display invoice number, amount in 1 table, amount in 2 table, and the different

 

what i could do it is to create a table, but i cant get data from 2 table and it is no work as should

 

CalculatedTable2 = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Total in CZ] ), VALUES ( ExtractTESTv4[Total in CZ] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Invoice.Nr] ), VALUES ( ExtractTESTv4[Document No.] ) ) )
RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Invoice.dtDate], "Amount XSP", [Total in CZ],"Invoice nr", [Invoice.Nr])))

 

in the screenshot, you can see that the table so create give me a result even if the amount it is same

Capture.PNG

 Capture.PNG

but looks that  display also invoice with the same amount

 

I think to depend on the fact that in the 1 table some invoice are split as shown in the image below (very few of them)

Capture.PNG

 and in the second table, there is just one line for this invoice ( the total amount are the same in both tables )

if it is possible to like this:

basically the sum of invoice number 0000 (table1)  = invoice number 0000 (table 2)

Could anyone help?

 

@Zubair_Muhammad    maybe 🙂

HI @snifer

 

Could you copy paste few rows of from each Table with Expected Ouput

For some reason I am not able to access Drop Box

 

For example

 

Table1

 

Employee IDSupervisro IDValidation Result
WS20017345382Valid
WS2002 Invalid
WS2003WS2001Valid
WS2004WS2004Invalid


Expected Output

 

??

 

Regards,
Zubair


table 1    table 2   
         
invoice nr  dateamountchange rate invoice numberdateamountchange rate
100001/01/2018101 100001/01/2018101
100102/01/201851 100102/01/2018151
100102/01/201851 100203/01/2018251
100102/01/201851     
100203/01/2018201     
 1003 04/01/2018 30 1     

 

so I need to check invoice number in both table and match that is the same amount for same invoice nr, if not should give as output:

invoice number, the amount in 1 table, amount in 2 table, and the difference between the amount, and with a column for change rate from table 1 and 1 column with change rate from table 2.

 

problem is that if you see on table 1 some invoice is split into multiple lines, in this case, the sum of the line with same invoice number in table 1 should be equal to same invoice nr amount in table 2.(always in one line)

 

+ in case that invoice number in table 1 is not present in table 2 than as output should have just the invoice nr and amount and change rate from table 1

 

@Zubair_Muhammad

@snifer

 

So in above case what is the Final Output you desire?

Can you put final output in Table form?

Regards,
Zubair


     
      
output     
Invoice number/amount table 1//change rate table 1//amount table 2//change rate 2//difference (amont table1-amount table 2)/
10022012515
1003301  30
      
      
     Total of all difference 35

 

this would be the output

@Zubair_Muhammad

@snifer

 

Please see attached file and let me know if it helps

Regards,
Zubair


@Zubair_Muhammad

 

I can't create an active connection between table 1 and table 2 for invoicenumber because I connected before those2 tables to a  calendar table(created by me) in order to filtrate this 2 table for the same time frame.(

 

 

how can i do to filtrate table at the same time for the specific time frame and create a correlation for invoice number?

Capture.PNGc

check error below

 

Capture.PNG

Zubair_Muhammad
Community Champion
Community Champion

@snifer

 

Same date period means...same day or same month or same year?

Regards,
Zubair


 it is the same date in this format

dd/mm/yyyy

 

I already did, a create a correlation with a calendar table created by me, now I need to  compare the invoice number column in the those different table, how I can do that?

 

Capture.PNG

i try to create this new column but i get an error

Transfered =
VAR Test =
COUNTROWS (
FILTER (
InvoiceLineTESTv4,
InvoiceLineTESTv4[Invoice.Nr] = ExtractTESTv4[Column4]
)
)
RETURN
IF ( Test = 1, "Yes", "No" )

 

A single value for column 'Column4' in table 'ExtractTESTv4' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

@snifer

 

HI,

 

Please try this calculated table

From the Modelling Tab>>>New Table

 

CalculatedTable =
CALCULATETABLE (
    EXCEPT (
        VALUES ( InvoiceLineTESTv4[Invoice Nr] ),
        VALUES ( ExtractTESTv4[Invoice Nr] )
    ),
    INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) )
)
Regards,
Zubair


@snifer

 

Please see attached sample file as well

 

snifer.png

Regards,
Zubair


Capture.PNG

#thank you so much @Zubair_Muhammad

looks to work,

now the calculated table give me the result for all the column ( all the period)

but I need to be filtered by table calendar Testv4, how I can do that?

 

now I get as result the missing invoice number(I that is very good) but I would like to see the details of this invoice store in the table Invoice line testv4

 

in your example my question it is on the calculated table you got result invoice nr, but I would like have also other info as the dateCapture.PNG

@snifer

 

Try this revision

Please see revised file as well

 

CalculatedTable =
VAR temp =
    CALCULATETABLE (
        EXCEPT (
            VALUES ( InvoiceLineTESTv4[Invoice Nr] ),
            VALUES ( ExtractTESTv4[Invoice Nr] )
        ),
        INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) )
    )
RETURN
    GENERATE (
        temp,
        CALCULATETABLE (
            SELECTCOLUMNS (
                InvoiceLineTESTv4,
                "Date", [Date],
                "Other Column1", [Other Column.1]
            )
        )
    )
Regards,
Zubair


View solution in original post

@Zubair_Muhammad

thank you so much !

@ @Zubair_Muhammad

 

 

thank you for the solution you offered me,

 

if i wanna add compare the date the number invoice and the amount($) , how i can add the parameter amount the formula? 

in the calculated table now i dispplay date just from 1 table can i add the amount of the 2 table as well? how?

thank u

 

i try to 
CalculatedTable2 = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Invoice.Nr] ), VALUES ( ExtractTESTv4[Document No.] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Total in CZ] ), VALUES ( ExtractTESTv4[Total in CZ] ) ) )
RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Invoice.dtDate], "Total XSPED", [Total in CZ],"Invoice Reference", [Invoice.Reference] ) ) ) 

 

 

what i want to compare invoice number to see if same invoice have same amount

HI @snifer

 

Try this calculated Table

As I understand,, you want to add Amount from Table2 where Date and Invoice Number match..Right??

 

 

CalculatedTable2 =
ADDCOLUMNS (
    InvoiceLineTESTv4,
    "Table2 Amount", CALCULATE (
        SUM ( ExtractTESTv4[Amount] ),
        FILTER (
            ExtractTESTv4,
            [Invoice Nr] = InvoiceLineTESTv4[Invoice Nr]
                && [Date] = InvoiceLineTESTv4[Date]
        )
    )
)
Regards,
Zubair


so this is the link to file I'm using, so it will be more clear

 

https://www.dropbox.com/s/5crm2812zkkoekv/Test2.pbix?dl=0

 

the part number 1 i used your code to check the invoice number and the date,  looks to work fine!

 

on part 2 I need to check the invoice number and the amount ( sometime invoice on table one have a different amount of invoice in table 2)

 

if one amount is different I need to display:

invoice number, the amount in table 1, the amount in table 2 and the difference between them

 

@Zubair_Muhammad

 

image link

https://www.dropbox.com/s/mlpyc73bra6k33r/Capture.PNG?dl=0

 

 

1 problem is that checking the data some invoice on table one is split in more line (the same invoice in table 2 is always in 1 line)

so in case, the invoice is split in more line I need to check the some of this line with same invoice number = to invoice amounth in table 2 

 

example of split invoice check invoice number 40001412 ( is on 3 lines) in table 1 (InvoiceLineTESTv4)

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.