topic Re: DAX Help - Calculating Count at Subtotal Level in DAX Commands and Tips

DAX Help - Calculating Count at Subtotal Level

redgun24 — Fri, 22 Nov 2019 21:11:14 GMT

Hi all,

New to DAX. I'm trying to only show conflicts (1, 2, 3, 4, 5) in the below visualization that have more that one entitlement (AP Invoice Entry, AP Supplier Master, etc.). For the example below the desired end result would be to see conflicts 3 & 5 disappear because they only have one entitlement rather than two.

I tried using a filter but realized that applied at the lowest level, in this case the entitlement. Is there a way to add a measure that would calculate the # of entitlements under a given conflict? I could then use this as a slicer or to filter.

Thank you!

Re: DAX Help - Calculating Count at Subtotal Level

Nathaniel_C — Fri, 22 Nov 2019 22:28:50 GMT

Hi redgun24 ,
If I understand, you want to filter the visual. Try this measure:

Show only = If(MAX(Conflicts[Counter])>1,MAX(Conflicts[Counter]),BLANK())

my table is Conflicts.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: DAX Help - Calculating Count at Subtotal Level

az38 — Fri, 22 Nov 2019 22:32:37 GMT

Hi redgun24

Try to add measure in your table

Measure = calculate(count('Table'[Entitlement]);ALLSELECTED('Table'[Entitlement]))

then in Filters pane add "Measure" to a "Filters on this visual" and check Measure is greater than 1

Re: DAX Help - Calculating Count at Subtotal Level

redgun24 — Mon, 25 Nov 2019 14:58:41 GMT

Hey Nathaniel,

Thank you for the reply. I tried the formula, assuming that your Counter is my Count of Entitlements but was getting the below error. Am I missing something?

Re: DAX Help - Calculating Count at Subtotal Level

redgun24 — Mon, 25 Nov 2019 15:17:02 GMT

Hey Az38,

I was able to get this to work. Thank you!

Az38 = calculate(count('Conflicts & Entitlements'[Entitlement]), ALLSELECTED('Conflicts & Entitlements'[Entitlement]))