Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JuradoKevin14
Frequent Visitor

DAX QUERY SELECTED VALUE

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

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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.

1.png2.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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.

1.png2.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.