Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Using a filter based on Multiple columns that can filter the whole report page ! including Measures

Greeting Everyone 

 

i want to create a filter that based on 3 columns ( the filter must be a single value and offer ID cant be duplicated )

here is a little example and if can anyone can try it with me please  : 

this is the table offer : 

 

Offer ID        NP1   NP2  NP3

Example 1Anullnull
Example 3ABnull
Example 4BCnull
Example 2ABC
Example 5FBnull
Example 6ACnull
Example 7SDA
Example 8FHnull

this is the filter that i will use : 

 

In my Report i want to Display a Measure called Number of offers . this measure should be filtered using the NP filter 

Example : 

if i choose the letter "B"  in the filter the number of offers should 4 .

if i choose H the number of offers should  be  1.

Can somone give me a trick or hints please ? i tried many tricks all i can filter is a list all the solution that i tried doesnt work with measures in the reports 

if this is not possible technically let me know please so i can inform my client ! 

Thanks in advance community 

Need Help
Show and Tell

2 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    Hi Anonymous ,

    This is a double post on your previous topic I think --> LINK

    What I was going to answer there was that you will need to unpivot. I understand it is difficult and you need to rethink your datamodel design, but since you are working with Measures it should be possible but you'll need to change the measures you have. On the other hand, what you absolutely can do is this:

    In your original query, add an INDEX column. (Power Query Editor, Add Column ribbon, Index Column. Then duplicate the query and do the trick I showed you in your other topic (creating the three columns). Then unpivot and remove all columns except the Index column and the Value column (with all possible values). Then create a relationship between your original Query index column and the new query index column. Then use the the second query in your slicer and that should filter your original table (and keep your measures intact). 

     

    Let me know if it works!

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi Anonymous ,

    As mentioned by JarroVGIT , I think the easiest way is to apply Unpivot Columns, and then use the count function to achieve the result you want:

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYcAU6C2hcdAqxZRIS2is1QBdfgFNkiGNYdFx_vwuWcb_g?e=wBvDnH

    Best Regards,
    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.