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 me...
Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    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.⚡

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.