Not getting valid results when using calculate with measure filter

[DeBIe]

Hello there, hope you are all doing well.

I'm trying to wrap my head around something, which is using a measure as a filter in the calculate function. I've understood dat I need to add FILTER to be able to use a measure as a filter in the calculate function, but it does not give me the results that I need.

#1 For my realization, I would like to know the total of invoices, with the condition that there have been 0 AutoPurchasedInvoices and 0 RepeatedPurchases. So for example when a supplier has 1000 invoices and only 1/1000 has AutoPurchasedInvoices=1 or RepeatedPurchases=1, then my result should be 0.

If another supplier has 50 invoices and 50/50 have AutoPurchasedInvoices=0 and RepeatedPurchase=0, then my result should be 50. (This measure should be named my PotentialInvoices).

#2 After this I also would like to write a measure which gives me the total of distinct suppliers that have PotentialInvoices.

What i've done so far is create these measures:

Total Invoices = Count(InvoiceID)

Total AutoPurchased Invoices = sum(IsAutoPurchased)

Total RepeatedPurchase Invoices = sum(IsRepeatedPurchase)

AutoPurchased+RepeatedPurchase = Total AutoPurchased Invoices + Total RepeatedPurchase Invoices 

Total PotentialInvoices = calculate(TotalInvoices),Filter([Table],AutoPurchased+RepeatedPurchase=0)

Total DistintPotentialSuppliers = ?

My data looks like this                              

InvoiceID	Country	SupplierName	IsAutoPurchased	IsRepeatedPurchase
1	US	Amazon	0	0
2	US	Amazon	1	0
4	US	Ebay	0	0
5	US	Wallmart	0	1
6	NL	Dixons	0	0
7	NL	Dixons	0	0
8	BE	Bruna	0	0
9	BE	Savoy	0	0

This is my desired result

Country	Total Invoices	Total PotentialInvoices	DistinctPotentialSuppliers
US	4	1	1
NL	2	2	1
BE	2	2	2

I appreciate you for looking into this and helping me. I think i'm missing some logic thinking in realizing this requirement.

[v-yalanwu-msft]

Hi, @DeBIe ;

I am not sure if I understand your logic correctly. According to my understanding, you can try to create the following measure.

Measure = IF( CALCULATE(SUM([IsAutoPurchased])+SUM([IsRepeatedPurchase]),FILTER(ALL('Table'),[Country]=MAX([Country])&&[SupplierName]=MAX([SupplierName])))=0,1,0)

Total PotentialInvoices = SUMX(FILTER(ALL('Table'),[Country]=MAX([Country])),[Measure])

Total DistintPotentialSuppliers = CALCULATE(DISTINCTCOUNT([SupplierName]),FILTER(ALL('Table'),[Country]=MAX([Country])&&[Measure]=1))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

Hey @v-yalanwu-msft , thank you for your reply.

It looks like your measures gives the right result in your model. However I forgot to mention that my country and supplier column comes from separate lookup tables. I don't know how to rewrite your first measure to get the Filter applied for two different tables (table Country and table Supplier). The values InvoiceID, total invoices, IsAutoPurchased and IsRepeatedOrder are in my fact table.

Do you know how I can rewrite this to make it work for my model?

Thank you so much.

[v-yalanwu-msft]

Hi, @DeBIe ;

I'm sorry that I'm not quite clear about your model structure. Could you please take a screenshot or modify it on my Simple?
If from different tables, what is their relationship? Will different countries have the same supplier name?

Best Regards,
Community Support Team_ Yalan Wu

[DeBIe]

I've set up a example model. I'm not sure if you can download the model. My first time doing this via google drive.

https://drive.google.com/file/d/1fjD9jzxNNf-SV-GcKzciKPKzgMhc3VVo/view?usp=sharing  

[v-yalanwu-msft]

Hi， @DeBIe ；

Try it;

measure = CALCULATE(SUM([IsAutoPurchased])+SUM([IsRepeatedOrder]),FILTER(ALL('Invoices_Fact'),[Country_id]=MAX([Country_id])&&[Supplier_id]=MAX([Supplier_id])))

Total PotentialInvoices = COUNTX(FILTER(ALL('Invoices_Fact'),[Country_id]=MAX([Country_id])&&[measure]=0),[InvoiceID])

Total DistintPotentialSuppliers = CALCULATE(DISTINCTCOUNT('Invoices_Fact'[Supplier_id]),FILTER(ALL('Invoices_Fact'),[Country_id]=MAX([Country_id])&&[Measure]=0))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DeBIe]

Hey @amitchandak 

Any other thoughts on why it is not working for me? I still can't get it to work and I have absolutely no clue what's going on here.

Thanks for your time.

[amitchandak]

@DeBIe , You need to force invoice context

calculate(Countx(Filter( values(Table[InvoiceID] ),[AutoPurchased]+[RepeatedPurchase]=0), [TotalInvoices]))

[DeBIe]

Hi @amitchandak ,

Thank you for replying. I have implemented your suggested measure but it does not give me the correct result. See the picture below. The record which I highlighted should be the only one that I want to show up.

The others should not be PotentialInvoices because they have AutoPurchased or RepreatedPurchase >0.

This is the measure for potentialinvoices: 

PotentialInvoices = CALCULATE(COUNTX(FILTER(VALUES(Table[InvoiceID]),([AutoPurchased]+[RepeatedPurchase])=0),[TotalInvoices]))