DAX QUERY SELECTED VALUE

[JuradoKevin14]

Hi I am not sure how to attain this.

I have a dim_Type which consist of 

Type            Carrier
A                Carrier1

B                Carrier2

C                Carrier3

D               Carrier4

Then I have a Transactional Table 

Date                Type                  Amt
12/02/3            A                        50

11/03/3            B                        20

10/05/3            C                        10

05/04/3            D                        150

02/05/3            E                        350

12/02/3            A                        50

11/03/3            B                        20

10/05/3            C                        10

05/04/3            D                        150

02/05/3            E                        350

Dropdown Filter : A
Output:

 A                              100

Carrier 2                    40

Carrier 3                    20

Carrier 4                    300

Carrier 5                    700

the if 

Dropdown Filter : B
Output:

Carrier 1                    100

B                                40

Carrier 3                    20

Carrier 4                    300

Carrier 5                    700

[v-shex-msft · Accepted solution]

HI @JuradoKevin14,

You can create a calculated table to summary two table records and use this to create a table visual.

NewTable = 
UNION (
    SUMMARIZE ( dim_Type, [Carrier], "Total", SUM ( Transactional[Amt] ) ),
    SUMMARIZE ( dim_Type, [Type], "Total", SUM ( Transactional[Amt] ) )
)

Then you can use raw 'dim Type' table 'type' field as source to create a slicer and write a measure formula to receiver filter effect and compare with current table records to return flag.

flag = 
VAR selectedType =
    VALUES ( dim_Type[Type] )
VAR reverseCarrier =
    CALCULATETABLE (
        VALUES ( dim_Type[Carrier] ),
        FILTER ( ALL ( dim_Type ), NOT ( [Type] IN selectedType ) )
    )
VAR _current =
    SELECTEDVALUE ( NewTable[Carrier] )
VAR allCarrier =
    ALL ( dim_Type[Carrier] )
RETURN
    IF (
        COUNTROWS ( EXCEPT ( ALL ( dim_Type[Type] ), selectedType ) ) = 0,
        IF ( _current IN allCarrier, "Y", "N" ),
        IF ( _current IN UNION ( selectedType, reverseCarrier ), "Y", "N" )
    )

After these steps, you can use the flag measure on table 'visual level filter' to filter records based on slicer selections.

Regards,

Xiaoxin Sheng