Help with DAX distinctcount / filter / all please

[jeffhickman]

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

[Anonymous]

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]

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]

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

[jeffhickman]

Thanks again for staying with me - I'll get a pbix uploaded as soon as I can. Bear with me, I haven't forgotten but it'll take me a little while to create an example with no real data in it.