Forum Discussion

sleekpeek's avatar
sleekpeek
Regular Visitor
4 years ago

Count Blank Values with Others in Measure

I have the query below. For segment, I need to count 2 values (value 1 and value 2) plus any blank values into the query.   Can I do this?
 
[i've amended the table and field values below to generic ones]
 
CALCULATE(SUM(Opp[ARR]), FILTER(Opp, Opp[StageName] IN { "3", "4}), FILTER(Account, Account[Segment] IN { "value1", "value2"}),USERELATIONSHIP(date[Date])) etc
 
Thanks
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    sleekpeek , try like

     

    CALCULATE(SUM(Opp[ARR]), FILTER(Opp, Opp[StageName] IN { "3", "4}), FILTER(Account, ( Account[Segment] IN { "value1", "value2"}  || isblank(Account[Segment])   ) ),USERELATIONSHIP(date[Date]))

      • sleekpeek's avatar
        sleekpeek
        Regular Visitor
        4 years ago

        when I add in BLANK() the query accepts it, however the values do not change and i'm missing a considerable amount 🙂 I've checked my visual, page and all pages filters and i'm not filtering anywhere to exclude these blanks.

         

        when exporting, it looks like this:

        The segment field references the field to the left ... should I add something to the segment field instead?

         

        any other suggestions guys .. thanks so far

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    sleekpeek  Can you try the following

     

    CALCULATE(SUM(Opp[ARR]), FILTER(Opp, Opp[StageName] IN { "3", "4}), FILTER(Account, Account[Segment] IN { "value1", "value2", BLANK()}),USERELATIONSHIP(date[Date]))