Filtering by a measure

[rubus_fruti]

Hallo, 

I have a problem with filtering by a measure and after some extensive searching on this forum, I decided to ask you for help. 

Here is my problem:

I have a table like this:

product	brand	date
a	A	x1
b	A	x2
c	A	x3
d	B	x4
e	B	x5
f	C	x6

I want to calculate distinct count of product and distinct count of brands, but only for cases where there are AT LEAST TWO products per brand. So my count of brands in this case should be 2, and my count of products should be 5. 

What I figured so far:

It has to be a measure, I cannot use calculated column, because I have many filters (slicer on date, slicers on other columns in table "Brands" that relates to this table etc.) so the final dataset depends on what a user chooses in all slicers (I'm not sure about using calculated table, but I guess it will face the same problems?)

I tried to follow this advice:

https://community.powerbi.com/t5/Desktop/Filtering-help/m-p/467911

managed to make a measure similar to check1, but was not able to use it as a Visual level filter.

I have also read this:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

but do not understand it really and I am not sure if that is helpful in my case.

I will appreciate any help, thanks. 

[Ashish_Mathur]

Hi,

Getting 2 should not be a problem.  Why should the distinct count of products be 5?

[rubus_fruti]

@Ashish_Mathur

because I don't want to count product f because brand C has only one product and I am only interested in brands that have at least 2 products. Note that this can also change, when I filter only dates 1-5 then I amalso not interested in brand B, because it then has only one product, d (because e and f are filtered away by date slicer).

[v-piga-msft]

Hi @rubus_fruti,

It seems that the first link you refered to should be right, I don't know why your measure cannot be used as a Visual level filter.

You could have a try with this formula below.

check1 =
VAR a =
    CALCULATE ( DISTINCTCOUNT ( Table1[B] ), ALLEXCEPT ( 'Table1', Table1[P] ) )
VAR b =
    CALCULATE ( DISTINCTCOUNT ( Table1[B] ) )
RETURN
    IF ( a >= 2 && b = 1, 1, 0 )

If you still need help, please share more details and your expected output.

Best  Regards,

Cherry

[rubus_fruti]

@v-piga-msft

I can create a measure like this, but when I drag it to page-level filters, I cannot drop it

I can drag and drop it to visual-level filters, but then when I click on this dropdown ("Show items when the value:") nothing happens, it does not drop down and I cannot fill any numbers in the box below this dropdown. 

I suspect that it is because I have many other slicers, e.g. slicer on date table that is linket to the date in this table here, so when I filter ony dates 1-5, the brand B should not be considered, so check1 will change. Does it make sense?

[v-piga-msft]

Hi @rubus_fruti,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft]

Hi @rubus_fruti,

---

I can create a measure like this, but when I drag it to page-level filters, I cannot drop it

I can drag and drop it to visual-level filters, but then when I click on this dropdown ("Show items when the value:") nothing happens, it does not drop down and I cannot fill any numbers in the box below this dropdown.  

---

Yes, measure cannot be used in Page level filter.

If it is convenient, could you share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best  Regards,

Cherry