Forum Discussion

Bimbone's avatar
Bimbone
Helper I
1 year ago
Solved

Filtering visuals using slicers combinations

(As I am getting no more reply to my previous threads, I will provide here the latest info as a new post)

 

I have a table called "CoppeCitte" with several columns, you can see below a sample:

 

 

 

Then, I have all the visuals in the below page:

 

 

You can see the three slicers: "Competizione" on the left (filtering the same column "Competizione"), "Scegli anno" in the middle (filtering the year in the column "Anno") and the cities on the right. This last slicer is actually filtering another linked table, but it gives the same value as the column "CittaMain".

If you look at the table in the centre bottom, what I want to achieve is that instead of showing all the records, it gets filtered on the "SFPair" which corresponds to the combination of the three slicers above.

 

I have been trying several methods using measures, but I cannot get the result I want.

 

Any further feedback or suggestion would be appreciated. Thanks!

Need Help
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    Hi Bimbone ,

    Thank you for reaching out to us on the Microsoft Fabric Community Forum.

    Please post regarding the same in the issues forum for wider visibility:

    Issues - Microsoft Fabric Community

    If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.



     

     

10 Replies

  • freginier's avatar
    freginier
    Solution Sage
    1 year ago

    Hey there!

     

    Here are a few solutions for you:

     

    1. Ensure Correct Relationships Between Tables
    - Verify that the Competizione, Anno (Year), and CittaMain (City) fields are properly related in your data model.
    - The main table (CoppeCitte) should be linked to the slicer tables via one-to-many relationships.
    - If using a separate slicer table, ensure it’s correctly related to CoppeCitte.

     

    2. Use a Measure to Filter "SFPairs" Dynamically
    Create a DAX measure to check if the selected slicer values match the records in the table:

    SelectedSFPairs =
    VAR SelectedComp = SELECTEDVALUE( CoppeCitte[Competizione] )
    VAR SelectedYear = SELECTEDVALUE( CoppeCitte[Anno] )
    VAR SelectedCity = SELECTEDVALUE( CoppeCitte[CittaMain] )

    RETURN
    IF(
    SELECTEDVALUE( CoppeCitte[SFPair] ) IN
    FILTER(
    CoppeCitte,
    CoppeCitte[Competizione] = SelectedComp &&
    CoppeCitte[Anno] = SelectedYear &&
    CoppeCitte[CittaMain] = SelectedCity
    ), 1, 0
    )

     

    Hope this helps!

    😁😁

    • Bimbone's avatar
      Bimbone
      Helper I
      1 year ago

      Hi freginier ,

       

      thank you for your reply. However, I have the following error when I create the measure you suggested:

      "The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression."

       

      Why would it happen?

      • freginier's avatar
        freginier
        Solution Sage
        1 year ago

        Hey there,

        This happens because the CONTAINSROW function requires all columns of the table expression to be specified.

        You need to ensure that all required columns are correctly referenced. Here’s how you can modify the measure:

        SelectedSFPairs =
        VAR SelectedComp = SELECTEDVALUE(CoppeCitte[Competizione])
        VAR SelectedYear = SELECTEDVALUE(CoppeCitte[Anno])
        VAR SelectedCity = SELECTEDVALUE(CoppeCitte[CittaMain])

        RETURN
        IF(
        CONTAINSROW(
        VALUES(CoppeCitte),
        CoppeCitte[Competizione], SelectedComp,
        CoppeCitte[Anno], SelectedYear,
        CoppeCitte[CittaMain], SelectedCity
        ), 1, 0
        )

         

        Then use SelectedSFPairs as a visual filter on the table and set it to 1 so only matching records are displayed.

         

        Hope this helps!

        😁😁