Forum Discussion

jeffhickman's avatar
jeffhickman
New Member
7 years ago

Help with DAX distinctcount / filter / all please

Hello all 

Please could I get some assistance figuring out some syntax which eludes me - I've read and tried many things but am stuck. 

I have a table (PostcodeDemog) which contains UK postcode data. For each postcode there is a demographics field too, something like this -

 

Postcode, DemoCat

AB1 2AB, aaa

AB1 3AB, aaa

AB1 4AB, bbb

AB1 5AB, ---  // these are reserved postcodes 

AB1 6AB, --- // with no democat available

 

I need a chart which has a distinctcount of PostCodes on one axis, with the DemoCat value on the other axis. 

This is easy enough of course.  But I need this chart -

  • to always remain static, regardless of whatever filters/slicers are applied (so I think I need to use ALL in my dax somewhere)
  • to exclude rows where DemoCat = "---" (so I think I need to use FILTER too) 

I'm trying to make a measure that includes this logic, but I cannot get the syntax right... I am trying to get some combination of the following:

  • DISTINCTCOUNT('PostcodeDemog'[PostCode])
  • where PostcodeDemog[DemoCat]<>"---"
  • ALL -- not sure how to use this; at all times I want all possible values in 'PostcodeDemog'[PostCode] 

Hoping someone can help me out. 

Many thanks

Jeff

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi jeffhickman,

     

    Please try to use below dax measure formula if it suitable for your requirement:

    Measure =
CALCULATE (
    DISTINCTCOUNT ( 'PostcodeDemog'[PostCode] ),
    FILTER ( ALL ( 'PostcodeDemog' ), PostcodeDemog[DemoCat] <> "---" )
)

    Regards,
    Xiaoxin Sheng

    • jeffhickman's avatar
      jeffhickman
      New Member
      7 years ago

      Hello - thanks so much for your reply.  I no longer get a syntax error in my measure, but the chart is showing the same value for everything on the DemoCat axis - it's showing the grand total of the measure on all bars, rather than the measure per DemoCat. 

      I wonder if you can help a bit further?

      Many thanks

      Jeff

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI jeffhickman,

         

        I'd like a pbix file with some sample data for test, it will be help for coding formula on your scenario.(you can upload to onedrive or google drive then share link here, remember do mask on sensitive data)

         

        Regards,

        Xiaoxin Sheng