Forum Discussion

Farwest's avatar
Farwest
Frequent Visitor
6 years ago
Solved

Column selection based on Filter

Dear Power BI community,   I am having a hard time setting a dynamic filter on a report.   I am trying to use a filter in order to set a measure, it is a single selection filter.   Selected = i...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Farwest ,

    Please try to complete the following steps to achieve your requirement:

    1. Create one dimension table for name field(Group 1,Group 2,Group 3 etc. )

    Create table Groups

    2. Create the relationship between two tables using field "Group" in Groups table and "Name" field in Table

    Create relationship between dimension and fact table

    3. Create a measure and drag the related fields onto the visual as below screen shot

    Measure = IF(ISFILTERED('Groups'[Group]),MAX('Table'[Name 2]),MAX('Table'[Name 1]))

    Create measure and table visual

    Best Regards

    Rena

Farwest's avatar
Farwest
Frequent Visitor
6 years ago

Thanks amitchandak 

 

This actually works better, but I still have a problem.

 

IF(HASONEFILTER('Table' [Name]);
SWITCH(SELECTEDVALUE('Table' [Name]);
"Selected"; MAXX('Table' [Name 1]);
MAXX('Table' [Name 2])
);
MAXX('Table' [Name 2])
)

 

This works when I define the selected Filter Value "Selected", but I am trying  to:

if only one filter is selected then get 'Table' [Name 1] otherwise (all selected) get 'Table' [Name 2]  (this is a single filter selection anyway)

 

 

amitchandak's avatar
amitchandak
Super User
6 years ago

Farwest ,

Try like


IF(isfiltered('Table' [Name]) && HASONEFILTER('Table' [Name]);
SWITCH(SELECTEDVALUE('Table' [Name]);
"Selected"; MAXX('Table' [Name 1]);
MAXX('Table' [Name 2])
);
MAXX('Table' [Name 2])
)

  • Farwest's avatar
    Farwest
    Frequent Visitor
    6 years ago

    amitchandak 

     

    Tried out as well but not really reaching the expected result.

    I am adding more details to better exlain the case, the result I have today is a bit different.

    When no filter is selected group 3 is still showing Name 1 table where it should be Name 2 as per the DAX on previous post.

    Every time one group is filtered I would liket o get Table[Name 1]

    Every time no filter is selected I would like to get Table [Name 2]

     

    I have hundreds of groups so I don't want to enter them one by on in the SWITCH like SWITCH(SELECTEDVALUE('Table' [Name]);
    "Group 1"; ...."Group 2";...

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Farwest ,

      Please try to complete the following steps to achieve your requirement:

      1. Create one dimension table for name field(Group 1,Group 2,Group 3 etc. )

      Create table Groups

      2. Create the relationship between two tables using field "Group" in Groups table and "Name" field in Table

      Create relationship between dimension and fact table

      3. Create a measure and drag the related fields onto the visual as below screen shot

      Measure = IF(ISFILTERED('Groups'[Group]),MAX('Table'[Name 2]),MAX('Table'[Name 1]))

      Create measure and table visual

      Best Regards

      Rena