Calculate Filter problem

DAX DAX Calculations
1

Hi,
I am trying to filter my data by Opportunity ID and then type Cat and Dog, my formula is is not providing any data.

image

Amount Cat & Dog =
CALCULATE([Amount],
FILTER(
VALUES(Data[Opportunity Id]),
COUNTROWS(
FILTER(‘Type’,
[Type]=“CAT” &&
[Type]=“DOG”))>0))

This is the result I am trying to get
image

In addition ( I haven’t got to this yet), I would also like all types associated with Cat
image

Any guidance would be greatly appreciated…I think I am closeDNA test CalculateFilter.pbix (30.8 KB)

2

Hi @KimC, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

@KimC,

You were much closer than you even realized. Your simple amount measure was all that was needed. All I did was modified your data model slightly to add an Opportunities Dimension table, which I used for the rows of the matrix. Then just put Type in the column well, and [Amount] in the Values well of the matrix visual, and context does the rest for you - the complex measure you created was not necessary.

image

image

I hope this is helpful. Full solution file attached below.

4

Hi Brian,

On the result I would like worksheet, I had filtered the opportunity id to show the result I wanted. If I remove that filter, and change the type to CAT and DOG it doesn’t work. Same result as before:

image

image

5

@KimC,

Ack! My bad - totally missed the filter pane conditions. Let me rework this and get back to you.

Sorry for the oversight.

  • Brian
6

@KimC

I created different measures for every Type to get the type of matrix table you are looking to achieve. The measures will come into play when you are trying to filter the Type. For e.g. if looking to just get all the amount associated with Cat & Dog, you just have to drag the Cat & Dog measure in filter pane and set the filtering to is not blank as follows:

1
11920×1080 158 KB

There might be better solution than this but I get to make it work with this method. Attaching the pbix file and let us know if it satisfies your requirement.

eDNA Forum - DNA test CalculateFilter Solution.pbix (43.1 KB)

Thanks,

1 Like
7

@KimC,

Apologies for the earlier confusion. I think I’ve now got a full solution for you that makes no use of the filter pane. The key is the DAX expression below that creates a table of only the Opportunity IDs for which there is at least one type cat and one type dog.

image
image1018×420 21.7 KB

Cat and Dog =
VAR vTable = ADDCOLUMNS(
    SUMMARIZE(
        Opportunities,
        Opportunities[Opportunity Id]
    ),
    "@TotCat", [TotalCat],
    "@TotDog", [TotalDog],
    "@Amount", [Amount]
) 
VAR FiltvTable = FILTER(
    vTable,
    [@TotCat] >= 1 &&
    [@TotDog] >= 1
) 
VAR DistID = SELECTCOLUMNS(
    FiltvTable,
    "@ID", Opportunities[Opportunity Id]
) 
VAR Result = CALCULATETABLE(
    FILTER( ALL( Data ), Data[Opportunity Id] IN DistID )
) 

RETURN
Result

I hope this is helpful. Full solution file below.

1 Like
8

Thank you both, @BrianJ & @MudassirAli I really appreciate the time you have taken out of your day to find a solution to my problem. This example is a simplified version of my model and I think the solution from @MudassirAli will suit my needs the best and it is simple for my brain to understand and replicate :grinning:
You are the Dax king @BrianJ, that is some impressive code your wrote!
:pray: :pray: :pray: :pray: :pray: :pray: :pray:

9

@KimC,

Happy to help, and glad you got what you needed. I find it endlessly fascinating to see the different routes that people take to reach the same desired results. NIce work, @MudassirAli.

  • Brian
1 Like