Trying To Build A Slicer With "And"

[chq]

Hello,

I am trying to get the following dataset to output the number of ID#'s that meet a set of Type critera ... Ideally I would like to be able to toggle how many ID#s purchased "Theatre and Opera" or "Business AND Theatre"

ID#	Type	Amount
2	Theatre	$50
14	Theatre	$50
14	Business	$10000
2	Opera	$65
14	Opera	$95

Desired Outcome:

Slicer: Theatre AND Opera
Distinct Count ID#= 2
Total Spend#= $260

Slicer: Theatre AND Business

Distinct Count ID#= 1

Total Spend#= $10050


It is important that people are able to filter by the Types of their choosing as it will be used for multiple departments. Any ideas?

[MartynRamsden · Accepted solution]

Hi @chq 

Try the following measures:

Count IDs =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table1[ID#] ),
            "@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) )
        ),
        [@TypeCount] = CountSelTypes
    )
)
RETURN Result

Total Value =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
SUMX(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table1[ID#] ),
            "@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) ),
            "@Value", CALCULATE( SUM( Table1[Amount] ) )
        ),
        [@TypeCount] = CountSelTypes
    ),
    [@Value]
)
RETURN Result

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[PaulDBrown · Accepted solution]

@chq 

Here is an alternative solution:

The model:

 1) calculate the ditinctcount of types in the data table:

Distinctcount Types = DISTINCTCOUNT('DataTable'[Type])

2) calculate the Distinctcount based on the slicer selection (AND condition):

Distinctcount = 
VAR Ids = COUNTROWS('Type Slicer')

RETURN
CALCULATE(DISTINCTCOUNT('DataTable'[ID#]); 
            FILTER(ALLSELECTED('DataTable'[ID#]);
            Ids = [Distinctcount Types]))

3) Calculate the Amount based on the slicer selection (condition AND);

Amount for selected Types = CALCULATE([Sum of Amount];
                            FILTER('ID Slicer';
                            [Distinctcount] >0))

And you get this (The "ID" filed in the table visual is from the "ID Slicer" table):

and here is the PBIX file if you're interested:

Slicer with AND condition 

[MartynRamsden · Accepted solution]

Hi @chq 

Try the following measures:

Count IDs =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table1[ID#] ),
            "@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) )
        ),
        [@TypeCount] = CountSelTypes
    )
)
RETURN Result

Total Value =
VAR SelTypes = VALUES( Table1[Type] )
VAR CountSelTypes = COUNTROWS( SelTypes )
VAR Result =
SUMX(
    FILTER(
        ADDCOLUMNS(
            VALUES( Table1[ID#] ),
            "@TypeCount", CALCULATE( DISTINCTCOUNT( Table1[Type] ), VALUES( Table1[Type] ) ),
            "@Value", CALCULATE( SUM( Table1[Amount] ) )
        ),
        [@TypeCount] = CountSelTypes
    ),
    [@Value]
)
RETURN Result

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[parry2k]

@chq something is not clear in your output, in case of 2nd choice, Theatre and Business, isn't the distinct ID count will be 2 instead of 1? 

[chq]

No as only ID# 14 will meet the AND requirements.

[parry2k]

@chq so based on Ids are common in the selected Type ,  dont think it means distinct