double filter for same dimension

[Anonymous]

Hi all,

I know the subject looks weird. 

First of all i don't know is it possible or not.
It might be difficult for me to write that. I'm using images to tell.

I need to create 2 measure it affected by 2 material dimension filter. Then according to combinations of the choice, it should be return proper value.
Here is the some example about combinations.



Here is the example PBIX file.
Waiting for your helps..
Regards

[Anonymous · Accepted solution]

Try This

Double Filter for Same Dimension PBIX 

[Anonymous]

Hi @Anonymous, @parry2k, @hohlick 

All of yours solutions are work fine. Thank you for your helps. 
I marked 2 solution because their working logic is different.
I continued with @Anonymous solution.
I want to ask one more thing in there.
Is it possible to add one more case to Type parameter as "Exclude". It means it must include first material but it should not include second material.

[Anonymous]

Exclusion should also be possible. You have to add another value for "exclude" in the table where you have given "and" and "or" is listed.

Then accordingly, based on the value of exclusion, add one more IF condition or SWITCH to change the measures accordingly.

Nothing complicated. Writing down the logic/algorithim on paper first and then transferring them to DAX code will help if you have any confusion. Sometimes, our brains are good with pen and paper than on computer when working with logics.😀

[hohlick]

First of all, you need to create two disconnected dimensions for Material:

Slicer1 = DISTINCT(Invoice[Material])

and

Slicer2 = Slicer1

Then make inactive relationships to you Invoice table:

Inactive relationships

Then your measure Cost-1 will be:

Cost-1 =
IF (
    [Type Value] = 2,
    CALCULATE (
        SUM ( Invoice[Cost] ),
        USERELATIONSHIP ( Invoice[Material], Slicer1[Material] )
    ),
    CALCULATE (
        SUM ( Invoice[Cost] ),
        USERELATIONSHIP ( Invoice[Material], Slicer1[Material] ),
        CALCULATETABLE (
            VALUES ( Invoice[Invoice] ),
            USERELATIONSHIP ( Invoice[Material], Slicer2[Material] )
        )
    )
)

and another almost the similar:

Cost-2 =
IF (
    [Type Value] = 2,
    CALCULATE (
        SUM ( Invoice[Cost] ),
        USERELATIONSHIP ( Invoice[Material], Slicer2[Material] )
    ),
    CALCULATE (
        SUM ( Invoice[Cost] ),
        USERELATIONSHIP ( Invoice[Material], Slicer2[Material] ),
        CALCULATETABLE (
            VALUES ( Invoice[Invoice] ),
            USERELATIONSHIP ( Invoice[Material], Slicer1[Material] )
        )
    )
)

as you can see, they differ only for relationships selected.

[Anonymous · Accepted solution]

Try This

Double Filter for Same Dimension PBIX 

[parry2k · Accepted solution]

@Anonymous solution attached.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k , How did you attach the file. Somehow I am unable to find the attach file option and I had to host it on onedrive and share the link. Can you tell me how to attach files here?