Passing a filtered table to Calculate function

[Anonymous]

Is it possible to pass a filtered table to calculate function . 

In other words: 

1- First filter the table by a specific column value e.g. IsCredit="YES" and remove any row with "NO" value

2- Pass that filtered table that has only rows where IsCredit="YES" to the Calculate function i.e. =Calculate(MIN(table1[date]),ALLEXCEPT(TABLE1,TABLE1[MEMBER_ID],TABLE1[date_Year])

I know I can filter a table through query editor but in my case I cannot do that because IsCredit is a calculated column.

I tried the following :

=Caclcualte(MIN(table1[date]),FILTER(ALLEXCEPT(TABLE1,TABLE1[MEMBER_ID],TABLE1[date_Year]),IsCredit="YES")

But it does not seem to work. Because the FILTER function gets applied after the calculation is completed using ALLEXCEPT filteration .

Any help 🙂 ?

Thank you

[AkhilAshok]

Why don't you try putting IsCredit column as well to ALLEXCEPT?

[Anonymous]

@AkhilAshok  that is what I ended up doing. What is really frustrating is that as a devloper I do not know the order of excution for a function without the need to test it .

calculate(distinctcount(table1[C1]),ALLEXCEPT(table1,table1[C2]),table1[C3]="x")

does the filtering happen first (table1[C3]="x") and then ditinct count with allexcept or the other way around?

Microsoft should have better documentaiton

Also i found out that I can do the following (adding more filters with OR)

calculate(distinctcount(table1[C1]),ALLEXCEPT(table1,table1[C2]),table1[C3]="x" ||table1[C3]="y")

but you cannot add two different filters like the following:

calculate(distinctcount(table1[C1]),ALLEXCEPT(table1,table1[C2]),table1[C3]="x" ||table1[C4]="y")

[TeigeGao · Accepted solution]

Hi @Anonymous ,

>>does the filtering happen first (table1[C3]="x") and then ditinct count with allexcept or the other way around?

No, If two or more filter arguments affect the same column, they are merged together using an AND operator.

>>Microsoft should have better documentaiton

There is a fantastic book which describe these problem, it is "The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI".

>>but you cannot add two different filters like the following

You can try to use AND() and OR() function to replace the && and ||

Best Regards,

Teige

[Ashish_Mathur]

Hi,

Share some data and show the expected result.