Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dax for variable text by selected Value

Hello together,

 

i have the following filter Box:

 

NumberCountry
1Germany
1China
2USA

 

I have a filter for Country.

Now my problem:

 

 - filter by China -> Text = "only China"

 - filter by USA and Germany -> Text = "without China"

 - no filter set -> "All selected"

 

Dax:

 

Filter_TEST = IF(
SELECTEDVALUE(table1[Country]) = "China";
"only China";
IF(
DISTINCTCOUNT(table1[Country])>DISTINCTCOUNT(table1[Country])-1;
"without China";"All selected"
))
 
Problem:
When i set the filter my quantity of distinct also decrease. So i need a quantity of distinc which is related to the unfilterd table or somebody has a other solution? Important is that it can be more then 3 or 5 contries
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hey Anonymous 

     

    Your solution is actually simpler than you think since you can just create a CONCATENATEX function to combine the selected values in the filter. See the thread below: https://community.powerbi.com/t5/Desktop/Card-visual-with-multiple-selection-slicer/td-p/678090

     

    If you need it to say "Without China" and "All Selected" you can just stick your IF function around the CONCATENATEX instead of your DINSTINCTCOUNT function. I don't like that idea because you will need an IF statement for each scenario. If this is what you are looking for then I recommend create another table for each scenario and the ntext which you would like to display and then using a LOOKUPVALUE function to find the text you need.

     

    If I am misunderstanding and you are looking to display the text on your filter then that is more complicated, but you can use the ideas above to create concatenated search and filter parameters. If you are trying to create a filter based on the slicer I recommend reading this thread: https://community.powerbi.com/t5/Desktop/Dynamic-Slicer-based-on-Slicer/td-p/429694

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hey Anonymous 

     

    Your solution is actually simpler than you think since you can just create a CONCATENATEX function to combine the selected values in the filter. See the thread below: https://community.powerbi.com/t5/Desktop/Card-visual-with-multiple-selection-slicer/td-p/678090

     

    If you need it to say "Without China" and "All Selected" you can just stick your IF function around the CONCATENATEX instead of your DINSTINCTCOUNT function. I don't like that idea because you will need an IF statement for each scenario. If this is what you are looking for then I recommend create another table for each scenario and the ntext which you would like to display and then using a LOOKUPVALUE function to find the text you need.

     

    If I am misunderstanding and you are looking to display the text on your filter then that is more complicated, but you can use the ideas above to create concatenated search and filter parameters. If you are trying to create a filter based on the slicer I recommend reading this thread: https://community.powerbi.com/t5/Desktop/Dynamic-Slicer-based-on-Slicer/td-p/429694