How to calculate Dynamic measure & Count for below scenario in PBI Desktop based on Slicer selection

[Anonymous]

I have one table with Product , Customer, & Sales details. Plus a Product Line Dropdown multi-select Slicer in PBI Desktop page.

1. User need to select 4 product lines only, not more and not less. When ever they select 4 PL's, 2 bar graphs needs to be display based on "Revenue" & another measure based on "Count"

a . one with "Customers who bought 2 out of the 4 selected PL's"

b. one with "Customers who bought 3 out of the 4 selected PL's"

c. Display error messsage, if they selected more or less PL's like "Choose only 4 PL's"

Please advice..

Thanks in advance!

[Anonymous]

HI @Anonymous ,

Can you please share some dummy data with the expected result for test?

How to Get Your Question Answered Quickly 
Regards,

Xiaoaxin Sheng

[Anonymous]

@Anonymous , 

Thanks for your quick response. Here is the sample data and my expected Expected Result. I hope this explation helps you.

https://drive.google.com/open?id=1UFOlVNvym9SBjlprHnJSkvA_8RfnAJVv 

@v-cherch-msft , Any help will be appriceated. 

Thanks in advance!

[Anonymous]

Hi @Anonymous ,

#1, You only need to create a table visual with 'account id' and count of 'account id' and create a slicer with 'product line' as source fields to interact with table visuals. (when you filter on 'product line' slicer, it will also apply filter effect to table visual)

#2, I'd like to suggest you create a measure with if statement to replace revenue fields, then it will be filtered if you selections items are less than 4 products.

Measure = 
IF (
    COUNTROWS ( ALLSELECTED ( 'Table'[Product Line] ) ) >= 4,
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[Account ID] )
    ),
    0
)

BTW, I also attached the sample file below, you can check it if you are confused above descriptions.

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous ,  i have another problem now Shex. 

When i pull Product line in to table, the data in table going blank. Please take a look at enclosed screenshot & PBIX file in below google drive and advise

https://drive.google.com/open?id=1sCbVVOr2hHFyGnnTF1NeLXbnw35j-pi8

Thanks in Advance!

[Anonymous]

HI @Anonymous ,

I modify the measure filter formula to let it works on your scenario, you can cancel your 'count' field filter and use measure on 'visual level filter' to filter on '1' tag.

Count filter =
VAR target = 1
RETURN
    IF (
        CALCULATE (
            COUNT ( 'Table'[Account ID] ),
            ALLSELECTED ( 'Table' ),
            VALUES ( 'Table'[Account ID] )
        ) > target,
        1,
        0
    )

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous 

Thank Shex!

This is looking good. But, i want to show case revenue for only Account Count in (2,3) , rest should be ignored. Please Advise!

Also, can we restric the slicer selection to 4 options only?

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can write a measure to compare current row contents and return tags, then use it on 'visual level filter' to filter records.

Count filter = 
VAR target = 1 
RETURN 
IF ( COUNT ( 'Table'[Account ID] ) > target, 1, 0 )

Notice: you can modify the target variable to change the filter range.

Regards,

Xiaoxin Sheng