topic Re: Help - Dax Threshold in DAX Commands and Tips

Help - Dax Threshold

varelapablo90 — Fri, 25 Mar 2022 09:00:56 GMT

Hello everyone

I need help with the following case.

I would like to know from my main raw data table how many of these invoices are under 50, how many under 100 (including the <50.... and so on 200, 300, etc.

Is there any possible way to create a table with a such information?

Currently I have my Threshold table with the values you see in the screen relationed to the main table on the Invoice Value field.

How could this be achieved?

Re: Help - Dax Threshold

BeaBF — Fri, 25 Mar 2022 09:12:59 GMT

varelapablo90 can you paste your invoice value field?

Thx,

Re: Help - Dax Threshold

varelapablo90 — Fri, 25 Mar 2022 09:19:12 GMT

Like this? These fields are in my main raw data table T_RawData_Azure

Re: Help - Dax Threshold

BeaBF — Fri, 25 Mar 2022 09:25:57 GMT

varelapablo90 Try with this calculated column in the Threshold Table:

Colonna =

VAR THRE = Threshold[Threshold]

RETURN CALCULATE(COUNTROWS(T_RawData_Azure),T_RawData_Azure[Amount_in_EUR] <= THRE)

It returns the count of amount value in T_RawData_Azure that are less or equal to that threshold, like this:

Re: Help - Dax Threshold

varelapablo90 — Fri, 25 Mar 2022 10:08:25 GMT

~~Worked perfectly, thank you!~~

What if I want to also have the column with the values in eur too?

Re: Help - Dax Threshold

varelapablo90 — Fri, 25 Mar 2022 09:59:14 GMT

I thought it had worked but now that I see it didnt, I have like 4 million rows in this set, numbers should be bigger, but also for example the Category 100 should include the 50. What be wrong?

Re: Help - Dax Threshold

BeaBF — Fri, 25 Mar 2022 10:14:00 GMT

varelapablo90 both threshold and amount are numbers?

Can you make an example with a subset? It seems very strange...

Re: Help - Dax Threshold

varelapablo90 — Fri, 25 Mar 2022 11:27:53 GMT

What do you mean with a subset?

I cant figure out what is different from the solution you provided, the datatypes are both numbers and the relationship is based on Amount_in_EUR -> Threshold

Re: Help - Dax Threshold

BeaBF — Fri, 25 Mar 2022 11:34:15 GMT

varelapablo90 the relationship is useless, try to remove it. By subset I mean taking a piece of data to understand why it doesn't work, to me it works with the data I pasted to you.

Try with Count instead of Countrows:

Colonna =

VAR THRE = Threshold[Threshold]

RETURN CALCULATE(COUNT(T_RawData_Azure[Amount_in_EUR]),T_RawData_Azure[Amount_in_EUR] <= THRE)

Re: Help - Dax Threshold

varelapablo90 — Fri, 25 Mar 2022 11:43:17 GMT

Got solved when deleting the relationship :S

Thank you very much