Forum Discussion

Sonam_Pillai's avatar
Sonam_Pillai
Helper I
6 years ago

How to exclude values

 

I have data like this:

 

CitiesCriteriaValue
MumbaiCriteria1 376
MumbaiCriteria2 null
ChennaiCriteria1 471
ChennaiCriteria2 254
DelhiCriteria1 null
DelhiCriteria2 481

 

pivot for better understanding:

CitiesCriteria1Criteria2Grand Total
Mumbai376 376
Chennai471254725
Delhi 481481

 

 so now when i am creating Clustered Column Chart i need cities having only Criteria 1. Means if in any city Criteria 1 is blank then i dont want graph to show that city. Currently it is showing Criteria 2 which i dont want. 

 

Basically from the above data i need details only for mumbai and channai. my clustered chart should show only these 2 cities with both critieria

 

any solution?

its better if i can do it throgh calculated column.

 

thanks in advance.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Sonam_Pillai , You can use visual level filter

     

    Or measure like

    Calculate(sumX(filter(table,table[city]<> ,"Delhi" Table[value]))

    • Sonam_Pillai's avatar
      Sonam_Pillai
      Helper I
      6 years ago

      Thank you. amitchandak 

       

      This is working in table graph but i am not able to convert the same table into Clustered chart.

       

      how to do it?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Sonam_Pillai,

    The best simple way is to use 'Criteria' field on your 'visual level filter' with advanced filter mode 'not' option and type the values which you do not want to be displayed.

    In addition, you can create a calculate table to extract the Criteria field values without relationship mapping and use it to create a slicer, then write a measure to filter records based on selection and use on raw chart visual level filter to filter records.

    Calculate table:
Selector =
VALUES ( Table[Criteria] )

Measure filter formula:
measure =
VAR selected =
    ALLSELECTED ( Selector[Criteria] )
RETURN
    IF (
        COUNTROWS (
            FILTER ( ALLSELECTED ( Table ), [Criteria] IN selected && [Value] <> null )
        ) > 0,
        "Y",
        "N"
    )

    Regards,
    Xiaoxin Sheng