topic Re: PowerBi dax FILTER, DISTINCT and REMOVEFILTERS in DAX Commands and Tips

PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

Tinus1905 — Mon, 08 Jul 2024 10:22:51 GMT

Hi,

Im struggling a little bit with DISTINCT and using a FILTER in my measure.

I have a table like this:

ID Type Option

123	Sale	Open
123	Service	Used
345	Sale	Closed
345	Service	Closed
345	Sale	Closed

On my report view I have a couple of slicers and a card.

In my measure; First I want to FILTER the column 'Type' to 'Sale', then I want to remove the duplicates on the column 'ID' and then ignore the slicer 'Option' that I have in my report view.

The following measure I have;

Measure1 = CALCULATE(
DISTINCTCOUNT(tableA[ID]),
tableA[Type] = "Sale",
REMOVEFILTERS(tableA[Option]))

The problem with this measure, that's what I expect, is that it first DISTINCTCOUNT the column and the uses a filter?

So I uses another measure like this; when I think it first FILTERS the column and then does the DISTINCT.

Measure2 =
VAR Filteredtable =
FILTER(
tableA,
tableA[Type] = "Sale")

VAR UniqueID =
DISTINCT(SELECTCOLUMNS(Filteredtable , "ID", tableA[ID]))

RETURN
COUNTROWS(UniqueID)

The problem with this measure is, I dont know how to use the REMOVEFILTER in this.

So can anyone tell me if Im correct that measure1 is the wrong measure I want to use and that the second measure is the correct one? And if the second one is the correct mneasure, how to get the REMOVEFILTER in it? Or is there a better measure that does the trick?

Re: PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

foodd — Mon, 08 Jul 2024 10:31:36 GMT

Remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Re: PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

Anonymous — Tue, 09 Jul 2024 06:13:31 GMT

Hi Tinus1905

Measure1 is correct.

For Measure2, I suggest you change it to the following:

Measure2 = VAR Filteredtable = FILTER(ALL('TableA'), 'TableA'[Type] = "sale") VAR UniqueID = CALCULATE(DISTINCTCOUNT('TableA'[ID]), REMOVEFILTERS('TableA'[Option]), Filteredtable) RETURN UniqueID

Here is the result.

Regards,

Nono Chen

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

Re: PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

Tinus1905 — Tue, 09 Jul 2024 07:38:33 GMT

Thanks. Can you tell me why you should use measure 1 or measure 2? Or is there no difference?

Re: PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

Anonymous — Tue, 09 Jul 2024 08:53:01 GMT

Hi Tinus1905

In fact, there is no fundamental difference, their results are all the same. Only the variable is used in Measure2.

In the case of large amounts of data, the use of variables may have some performance benefits.

It depends on your needs.

Regards,

Nono Chen

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

Re: PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

Tinus1905 — Tue, 09 Jul 2024 08:55:46 GMT

Ah, good to know. Thanks for your quick response.