How to count City where Persno count is <25

[Amardeep100115]

HI How can i get count of city where i can get output for Count of Persno is <25? sample data is available on below link https://1drv.ms/u/s!Ai3rGPgy20kLwRlVi7ohP0yYHHek?e=73Uw8I Thanks

[v-juanli-msft · Accepted solution]

Hi @Amardeep100115 

You could create measures

PRN count = CALCULATE(DISTINCTCOUNT(data[Persno]),ALLEXCEPT(data,data[City Pin Coad]))

<25_1 = CALCULATE(DISTINCTCOUNT(data[City Pin Coad]),FILTER(data,[PRN count]<25))

Or create columns

flag = CALCULATE(DISTINCTCOUNT(data[Persno]),FILTER(ALL(data),data[City Pin Coad]=EARLIER(data[City Pin Coad])))

<25_column = CALCULATE(DISTINCTCOUNT(data[City Pin Coad]),FILTER(ALL(data),data[flag]<25))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JIGAR]

@Amardeep100115 

Based on your pbix file sample data, use this DAX formula for your <25 Measure

<25 = CALCULATE(DISTINCTCOUNT(data[City Pin Coad]),FILTER(data,[PRN count] < 25))

This will give you count of distinct cities where person count is < 25

Hope this helps !!

Please mark it accepted if the solution was appropriate.

Thanks

[Amardeep100115]

Could you please share working Power BI file
Please

[JIGAR]

@Amardeep100115 

Here is the link

https://drive.google.com/open?id=1IcYWzBFhWuRYEX6hfmxLHwgJppp332Sn

Thanks