Forum Discussion

h11's avatar
h11
Icon for Helper III rankHelper III
2 years ago
Solved

Have a table Appear only few values when a filter is selected

Hello Team,   I have a question about filtering the data. I have two columns 1. Name, 2. Status. Initially I would like to show the status of all the names (Approved, In process and Completed) data...
Need Help
  • ExcelMonke's avatar
    ExcelMonke
    2 years ago

    You're welcome! If this is helpful, please mark it as a solution 🤠 
    In order to get what you need, you will need to do the following:

    1. In modeling, create a new table with the following DAX:

     

     

    VALUES(Table[Name])​

     

    And add this new column to a slicer on the page

     

    • Create a measure with the following:

     

     

    Measure = 
VAR _SelectedValue = SELECTEDVALUE('Table 2'[Name])
VAR _Filtered = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[Status] IN {"Approved", "In Progress"} && 'Table'[Name]=_SelectedValue)))
VAR _All = CALCULATE(DISTINCTCOUNT('Table'[Name]))

RETURN
IF(ISBLANK(_SelectedValue),_All,_Filtered)​

     

     

    • Lastly, on your table, add the measure filter to the visual filter pane and mark the filter as "is not blank"

    You should get this as result:

    Filtered: 

     

     

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago

Are the names of all your tables and columns correct? I would check those and make sure you double check the name of your Fact Table and columns

  • h11's avatar
    h11
    Icon for Helper III rankHelper III
    2 years ago

    ExcelMonke Yes Sir. I double checked everything and used exact same as you used including table and column names. Still facing the same syntax error.

  • h11's avatar
    h11
    Icon for Helper III rankHelper III
    2 years ago

    ExcelMonke Hi,

     

    I double checked the data and also tried to create this visual in a new file by using the data you used in the screenshot. But no luck. I'm 100% sure that I can get the results by following your approach. Could you please help me resolve this issue or try an alternate to get the desired results. Appreciate your help!

     

    Thanks