Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

double filter for same dimension

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

Need Help
Show and Tell

6 Replies

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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? 

  • hohlick's avatar
    hohlick
    Continued Contributor
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.😀