Simple Filter??

[macfuller]

I'm trying to create a calculated column to see how many price variations appear on a match exception report for a voucher. I can then filter on all voucher exceptions where the Invoice Price Mix > 1.

Invoice 123 has 3 different price variations - Higher, Same, and Lower. Invoice 456 has 2 variations - Lower, Same

Invoice	Line	PO Price	Voucher Price	Vchr Price Direction
123​	1​	1​	2​	Higher
123​	2​	2​	2​	Same
123​	5​	3​	2​	Lower
456​	1​	5​	4​	Lower
456​	2​	5​	5​	Same

But what i thought would be a simple calculated column is tying me in knots. The following DAX returns 3 for every row.

[Invoice Price Mix] =
VAR MyInvoice = 'Match Exception'[Invoice]
VAR PriceSwings =
    FILTER (
        VALUES ( 'Match Exception'[Vchr Price Direction] ),
        'Match Exception'[Invoice] = MyInvoice
    )
RETURN
    COUNTROWS ( PriceSwings )

What on earth am i missing for something so simple? Vchr Price Direction is also a calculated column - would that matter?

 

[JustynaMK]

I hope I understood your request correctly Try this calculated column:

Invoice Price Mix = CALCULATE(
    DISTINCTCOUNT('Match Exception'[Vchr Price Direction]),
    FILTER(ALL('Match Exception'), 'Match Exception'[Invoice] = EARLIER('Match Exception'[Invoice])
))

The result:

 

[macfuller]

Thanks for your reply. That measure code was running for 20 minutes to update a 280,000 row table before I used Task Manager to terminate. I tried a slimmer version...

VAR MyInvoice = 'Match Exception'[Invoice]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Match Exception'[Vchr Price Direction] ),
        FILTER (
            ALL ( 'Match Exception'[Vchr Price Direction] ),
            'Match Exception'[Invoice] = MyInvoice
        )
    )

But it returned 1 for everything instead of 3. I'm stumped. I'd like to use your code but it's taking too long!

 

[JustynaMK]

Ugh this is way too long!

What is the source of your data, is it SQL query? Are you willing to add this calculated column on load (i.e. by modifying your SQL query), or - even easier - by using M language in Power Query? Or is there any particular reason why you need to do it directly in PowerBI using DAX?

 

[macfuller]

It seems like it should be easy.

This is a cumulative collection of daily match exception reports generated by PeopleSoft. I don't know if Lawson or SAP are the same, but PeopleSoft won't retain the reason for an exception after it is resolved, so the historical daily reports (15,000+ lines each) are the only source of truth if you want to validate trends. I run a VBA macro that consolidates the daily reports into a single monthly file, so that's 15,000 x 30 days = 450,000 rows/month and I keep a year of data. So the query is going against a folder in which 12 Excel files of 450,000 rows each are kept. It might be quicker to dump them to .csv, but if the administrators add a column to the latest daily report it's easier to just insert a column in each of the last 12 monthly files than try to rebuild the .csv file.

The final tally is only 280,000 because every day that an exception is active it appears in the new daily file in exactly the same way, so that's 45 identical records for each day of 45 a transaction was in exception status. So after I pull in all the records I use M to give me the first and last date that a particular exception appeared, along with some other things, and eliminate the duplicates. The query already takes about 30 minutes to run, so adding the kind of merge you're thinking about so I could figure out how many types of price variation would add a lot more time. I then have a lot of LOOKUPVALUE and RELATED calculated columns to get related information on purchase orders, receipts, and payments. And my other tables pull in this data for advanced accrual calculations.

It's pretty Rube Goldberg, and if anyone has a better way to get the info I would be delighted. But that's life when you're using corporate systems.

I'm running this in Excel 2016 since that's what our company supplies us - I suspect Power BI would be quicker. Other similar calculated columns work fine so I just don't know why this one won't behave.