Calculate with Filtering on AccountID (hardcoded) is working but not working on AccountID (dynamic)

[Gazi_Sohan]

Hi everyone. I have a created a DAX measures in which there is a filtering on Account ID column of COADetails table. 

Now the issue is that, in the measure, when I write the Account ID numbers as hardcoded, the measure works, but when I write those dynamically, it doesn't work. To avoid confusion, I have created the same DAX measure twice, one being hardcoded and the other being dynamic - 

I am providing the necessary files i.e. the datasource (excel file) and the Power BI report which you will get in the below link - 
https://drive.google.com/drive/folders/1xDiFgNOm_OpwcqfcgNhInF1homob_suP?usp=sharing

Can anybody please tell me what am I doing wrong ?

[Anonymous]

Hi, @Gazi_Sohan 

here is my solution:

The condition you set in the measure's calculate function is incorrect, we also need to restrict the Category. To solve this wrong, use the following DAX expression：

Dynamic Accounts = 
var selectedrecord = SELECTEDVALUE('COA Summary'[Account ID])
RETURN
SWITCH(
    selectedrecord,
    106,
    CALCULATE(
        SUM(Transactions[Amount]),
        ALL('COA Details'),
        'COA Details'[Category]="Revenues"&&
        'COA Details'[Account ID] >= 'COA Details'[Operator Between Start]
        && 'COA Details'[Account ID] <= 'COA Details'[Operator Between End]
    ),
    206,
    CALCULATE(
        SUM(Transactions[Amount]),
        ALL('COA Details'),
        'COA Details'[Category]="Expenses"&&
        'COA Details'[Account ID] >= 'COA Details'[Operator Between Start]
        && 'COA Details'[Account ID] <= 'COA Details'[Operator Between End]
    ),
    CALCULATE(
        SUM(Transactions[Amount]),
        'COA Details'[Account ID] >= 'COA Details'[Operator Between Start]
        && 'COA Details'[Account ID] <= 'COA Details'[Operator Between End]
    )
)

A screenshot of the formula is shown below:

The results are as follows:

This way we can get the correct Total Expenses and Total Revenues by dynamic measure.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gazi_Sohan · Accepted solution]

Hey @Anonymous 

I have tried my own way by using a pretty simple approach i.e. using selectedvalue function and it's working perfect in the measure and made it dynamic - 

I have applied the same concept in the sample POC file that I provided for you (attached) - 

and it's also working fine in the sample file - 

But anyway, thanks for your help. I really appreciate it. You are a very talneted guy I must say.
Hats off 💥

.pbix file with my dynamic measure - 
https://drive.google.com/file/d/1jRhL9q_ytVP4OxSyjntFliLqdm_IXynP/view?usp=sharing

[Gazi_Sohan]

@Anonymous 
Thanks for your reply. 

In reality, we can't use the Category column because in the real report/file, we have to add Net Income which is not as easy as revenue/expense that you have shown in the filtering portion. Net Income is a combination of 7-8 categories including some blank categories in our main file.

We can't do this in our main file as because we only do have lots of Account Sub Categories when taken into consideration for calculating Net Income (Account Sub Category in the picture is basically the same as Category in the sample file that I provided) - 

Just like Revenue and Expenses (you included Category = "Revenue" and Category = "Expenses" within the calculate function), we can't do the same for Net Income (Category = "Net Income") as because it's not straightforward like Revenue and Expenses. Instead, Net Income is a combination of lots of categories as shown in the picture, it also includes blanks.

Also, if we use Category as a filter, then we actually don't need this part at all - 

'COA Details'[Account ID] >= 'COA Details'[Operator Between Start]
        && 'COA Details'[Account ID] <= 'COA Details'[Operator Between End]

because Category filter will automatically filter the accounts underneath.

Summary is - We can't use Category as a filter, instead we must have to anyhow use the AccountID, but it should not be hardcoded rather dynamic

[lbendlin]

What's the actual issue with the "Hardcoded Accounts"  measure?  Seems to be a reasonable approach.

[Gazi_Sohan]

Hey @lbendlin 
Sorry for the late reply to your question. Hardcoded means using raw numbers, for example, under the calculate function, we are using AccountID >= 400000 && AccountID <= 957090. Here 400000 and 957090 - these 2 are hardcoded numbers. 

Now, if you download the sample files, you will see that this numbers are present in the COA Details table under 2 columns - Operator Between Start and Operator Between End.

So, the summary is - we don't wan't to use AccountID >= 400000 && AccountID <= 957090 inside our measure, Instead we want to use AccountID >= Operator Between Start && AccountID <= Operator Between End.

Btw, we can't use the Category column and I explained the reason why (see my other replies).