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

Please try my custom visuals

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

Please try my custom visuals

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

Please try my custom visuals

     
      
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

Please try my custom visuals

@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

Please try my custom visuals

 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

Please try my custom visuals

@snifer

 

Please see attached sample file as well

 

snifer.png


Regards
Zubair

Please try my custom visuals

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

Please try my custom visuals

@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

Please try my custom visuals

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