topic Calculate disctinctcount as total and ignore filter in DAX Commands and Tips

Calculate disctinctcount as total and ignore filter

Tommyvhod — Thu, 19 Jan 2023 08:20:38 GMT

Hello all

I have a formula

produced Qty = CALCULATE(DISTINCTCOUNT(Data[ID]) , Data[step] = 1 || Data[step] = 2)

which calculates the total amount of produced items. From this I would like to calculate the percentage of faulty items.

On process step 1 and 2 it is ok, but the same item goes trough step 3,4,5 etc.

If I am filtering on the visual the next process step e.g step 4 i am getting a blank result.

I understand why - formula filters ste 1 and 2 from data and visual filter step 4. no result.

What I want to achieve is to keep the same result on the step 4 ( so ignore all visual filter )

I thing this is the ALL funtion,but I am having trouble to write it down.

PS> I also have different filters like date, item,...in the visual, which I would like to use.

So it should ignore only the process step visual filter

Thank you

Re: Calculate disctinctcount as total and ignore filter

FreemanZ — Thu, 19 Jan 2023 08:39:39 GMT

hi Tommyvhod

try like:

produced Qty = CALCULATE( DISTINCTCOUNT(Data[ID]) , KEEPFILTERS(Data[step] = 1 || Data[step] = 2) )

Re: Calculate disctinctcount as total and ignore filter

Tommyvhod — Thu, 19 Jan 2023 09:04:24 GMT

Unfortunately not working. Maybe because the distinctcount is counting ID and we are filtering the process step?

Re: Calculate disctinctcount as total and ignore filter

FreemanZ — Thu, 19 Jan 2023 12:38:49 GMT

hi Tommyvhod

what about this:

produced Qty = VAR _step = SELECTEDVALUE(data[step]) VAR _value1 = CALCULATE( DISTINCTCOUNT(Data[ID]) , Data[step] = 1 || Data[step] = 2 ) VAR _value2 = CALCULATE( DISTINCTCOUNT(Data[ID]) , Data[step] =4 ) RETURN IF( _step = 4, _value2, _value1)

Re: Calculate disctinctcount as total and ignore filter

Tommyvhod — Thu, 19 Jan 2023 13:06:06 GMT

If I understood correcly if the process is 4 than the number of parts there will be calculated.

The problem here is that always process 1 and 2 is used for the quantities.

It looks like: step 1 and 2 is the production, next steps are other steps like painting, control, packaging ( more than 20 steps) and for defects I would like to return the produced qty. ( step 1+2)