PowerBi dax FILTER, DISTINCT and REMOVEFILTERS

[Tinus1905]

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? 

[Anonymous · Accepted solution]

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.

[Tinus1905]

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

[Anonymous]

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.

[Tinus1905]

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

[foodd]

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-...

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/1447...

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