topic Re: Counting number of occurrences in DAX in DAX Commands and Tips

Counting number of occurrences in DAX

as1195 — Mon, 31 Jul 2023 12:47:41 GMT

Hi,

I am trying to find out total number of occurences of a Range measure .

I have a column called offset from the time table & for every offset, based on certain conditions am finding the range .Requirement is to count the number of occurenecs for each range as shown below in the expected output column.

I am using offset column and range measure in a table visual and am looking to solve this without creating any calculated table as connection method is Live.

Re: Counting number of occurrences in DAX

eliasayyy — Mon, 31 Jul 2023 13:29:40 GMT

Please try

Occursnces = CALCULATE(COUNT('Table'[column]),allexcept('Table','Table'[Range]))

Re: Counting number of occurrences in DAX

as1195 — Mon, 31 Jul 2023 13:52:18 GMT

Hi,
Thanks for the reply!
But I dont think this will work as Allexcept only takes table and column as arguments wherin in my case "Range" is calculated measure and not a column

Re: Counting number of occurrences in DAX

eliasayyy — Mon, 31 Jul 2023 13:55:04 GMT

ok can you please provide a sample dataset taht isnt confidential as well as your measures

Re: Counting number of occurrences in DAX

v-zhangti — Wed, 02 Aug 2023 02:54:22 GMT

Hi, as1195

You can try the following methods.

Measure:

Range = SWITCH(TRUE(), SELECTEDVALUE('Table'[Number])>=151&&SELECTEDVALUE('Table'[Number])<157,"151-157", SELECTEDVALUE('Table'[Number])>=139&&SELECTEDVALUE('Table'[Number])<145,"139-145", SELECTEDVALUE('Table'[Number])>=133&&SELECTEDVALUE('Table'[Number])<139,"133-139", SELECTEDVALUE('Table'[Number])>=127&&SELECTEDVALUE('Table'[Number])<133,"127-133")

Count = VAR CurrentRange = [Range] RETURN CALCULATE(COUNTROWS('Table'), FILTER(ALL('Table'), [Range] = CurrentRange ))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.