Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Power BI users,
I would like to know if the following is possible. First a bit of sample data:
CustomerID Product Month
1 Bread 201809
1 Fish 201809
2 Bread 201809
3 Fish 201809
SLICER
Bread X
Fish X
Current result: Expected result:
CustomerID Customer ID Month
1 " " 1: Bread 201809
1 " " 1: Fish 201809
2 " "
3 " "
How do I make it that I only show the customers that actually have all filtered values.
I'd love to hear from you!
Thanks in advance,
Foxxon
Solved! Go to Solution.
Thanks @Anonymous. An awesome new year to you too!
While answering your last question, I have realised there was a small mistake in the code for the last measure. It worked because of the data we had but to make it more general we need to add an ALL() on Month (change highligheted in red)
ShowMeasure2_V2 = VAR _EmptySlicer = CALCULATE ( ISFILTERED ( Table1[Product] ); ALLSELECTED ( Table1[Product] ) ) = FALSE () RETURN IF (NOT ( _EmptySlicer ); IF (CALCULATE ( DISTINCTCOUNT ( Table1[Product] ) ALLSELECTED(Table1[Product]); ALL(Table1[Month]) ) = COUNTROWS ( ALLSELECTED ( Table1[Product] ) ); 1 ) )
See it all at work in the attached file.
The reason why your measure was not working on the chart is because it had a small mistake. You can't do
ALL ( test_powerbi_marketbasket[Market Basket] )
cause you are wiping out the context filter you are interested in. It worked on the table (out of a bit of luck) because you have the order number in the rows and that determines the same filter as "Market Basket" in terms of "Product". So the only thing you need to do is remove that ALL():
MeasureV2 =
VAR _EmptySlicer =
NOT CALCULATE (
ISFILTERED ( test_powerbi_marketbasket[Product] ),
ALLSELECTED ( test_powerbi_marketbasket[Product] )
)
RETURN
IF (
NOT ( _EmptySlicer ),
IF (
CALCULATE (
DISTINCTCOUNT ( test_powerbi_marketbasket[Product] ),
ALLSELECTED ( test_powerbi_marketbasket[Product] )
)
= COUNTROWS ( ALLSELECTED ( test_powerbi_marketbasket[Product] ) ),
1
)
)
and then for the card you need another measure that uses the one above:
MEASURE V2TOT =
SUMX ( DISTINCT ( test_powerbi_marketbasket[OrderNumber] ), [Measure V2] )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks @Anonymous. An awesome new year to you too!
While answering your last question, I have realised there was a small mistake in the code for the last measure. It worked because of the data we had but to make it more general we need to add an ALL() on Month (change highligheted in red)
ShowMeasure2_V2 = VAR _EmptySlicer = CALCULATE ( ISFILTERED ( Table1[Product] ); ALLSELECTED ( Table1[Product] ) ) = FALSE () RETURN IF (NOT ( _EmptySlicer ); IF (CALCULATE ( DISTINCTCOUNT ( Table1[Product] ) ALLSELECTED(Table1[Product]); ALL(Table1[Month]) ) = COUNTROWS ( ALLSELECTED ( Table1[Product] ) ); 1 ) )
@Anonymous
What [ShowMeasure2_V2] does is:
1. Calculate how many of the products selected in the slicer the current CustomerID has (current CustomerID is the one in the row of the matrix at the moment the measure is executed). Let's call this A
2. Calculate how many products are selected in the slicer. Let's call this B
3. Compare A and B and return a 1 if A=B, a blank otherwise. Example:
- Slicer has bread: B=1. For IDs including bread, A=1, for the rest A=0
- Slicer has bread and fish: B=2. For IDs including bread and fish, A=2. For IDs including only bread or only fish, A=1. For all others, A=0
Only the rows in the matrix with 1 as result for the measure will be shown because of what we did on the visual level filter.
4. Additionally, we check whether anything is selected in the slicer (in the VAR _EmptySlicer). If nothing is selected, the measure returns a blank so that no row is shown.
Does that help?
Please mark the answer(s) as solution when we're done. So that others can have access to it as well.
and how about some kudos too? if you appreciate the answers
@Anonymous
To complement the previous explanation regarding the variable _EmptySlicer:
In order to check whether something at all is selected in a slicer, ISFILTERED(Column In the Slicer) should normally suffice.
Here, however, we have a trickier situation with [Product] in the slicer AND in the rows of the matrix. So ISFILTERED(Table1[Product]) will always be true in the rows of the matrix (except at the Total).
To overcome this, we use ALLSELECTED( ) in the filter argument for the CALCULATE. With that we override the effect of the matrix rows and force ISFILTERED(Table1[Product]) to only consider what is going on in the slicer.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |