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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Topn+rankx doesn't working with visual filter

Hi all

 

I have three table like that.

1-Table1

Customer

ValueA

A

2

B

3

C

45

D

4

C

3

B

1

A

2

C

45

D

7

E

0

B

8

C

0

A

3

E

0

F

53

T

21

N

2

B

0

C

8

 

2-Table2

Customer

ValueB

A

10

B

51

A

44

B

48

C

0

D

76

E

81

F

6

T

76

N

0

N

47

T

80

F

58

F

66

E

64

E

0

D

75

C

37

B

6

B

21

A

4

A

53

A

0

C

20

E

12

T

69

N

21

N

55

N

0

 

3-Table3

Customer

CustomerNo

A

1

B

2

C

3

D

4

E

5

F

6

T

7

N

8

 

Table1 and Table2 were joined to Table3 on Customer Columns.

 

I want to show this on matrix visual.

Table3.Customer - ValueA - ValueB

I'm applying a visual filter to ValueA like "Show items when the value: is not 0"
q1w2.jpg

Then when i try to apply topN filter to "Table3.Customer with sum of ValueB Top2" it only shows 1 rows.

Then i was tried to use RANKX function.

I was created this two measures.

Measure = RANKX(ALLSELECTED(Table3);[Measure 2];;DESC;Dense)

Measure 2 = SUM(Table2[ValueB])

 

Then i was added visual filter with [Measure] for show the value <=2 but i got same result.

 

Is there anyone have idea about this situation

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@Anonymous 

The underlying cause of this behaviour is that Visual Level Filters are computed independently, then intersected (you can use DAX Studio "All Queries" to verify this).

In your example:

  1. First filter is Customers where SUM ( ValueA ) <> 0, i.e. A,B,C,D,F,N,T
  2. Second filter is Top 2 customers ranked by SUM ( ValueB), i.e. E,T
  3. The intersection of these is just customer T

Unfortunately there's no way of telling Power BI the order in which to apply the Visual Level filters.

 

One solution is to create an awkward-looking measure like below that does the filtering in sequence, and add it as a Visual Level filter set to "= 1"

Customer Filter = 
// Returns 1 if current customer is in Top 2 for Sum ( ValueB ) after filtering Sum ( ValueA ) <> 0
VAR CurrentCustomer =
    SELECTEDVALUE ( Table3[Customer] )
RETURN
    IF (
        NOT ISBLANK ( CurrentCustomer ),
        VAR Customer_Allselected =
            CALCULATETABLE ( VALUES ( Table3[Customer] ), ALLSELECTED ( Table3 ) )
        VAR Customer_Allselected_ValueA_Zero =
            FILTER ( Customer_Allselected, CALCULATE ( SUM ( Table1[ValueA] ) ) <> 0 )
        VAR Customer_Top2_With_ValueA_Zero =
            TOPN (
                2,
                Customer_Allselected_ValueA_Zero,
                CALCULATE ( SUM ( Table2[ValueB] ) ), DESC
            )
        RETURN
            IF (
                CONTAINS ( Customer_Top2_With_ValueA_Zero, Table3[Customer], CurrentCustomer ),
                1
            )
    )

There could be other ways of writing this, but this worked for me in a model using your sample data.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

@Anonymous 

The underlying cause of this behaviour is that Visual Level Filters are computed independently, then intersected (you can use DAX Studio "All Queries" to verify this).

In your example:

  1. First filter is Customers where SUM ( ValueA ) <> 0, i.e. A,B,C,D,F,N,T
  2. Second filter is Top 2 customers ranked by SUM ( ValueB), i.e. E,T
  3. The intersection of these is just customer T

Unfortunately there's no way of telling Power BI the order in which to apply the Visual Level filters.

 

One solution is to create an awkward-looking measure like below that does the filtering in sequence, and add it as a Visual Level filter set to "= 1"

Customer Filter = 
// Returns 1 if current customer is in Top 2 for Sum ( ValueB ) after filtering Sum ( ValueA ) <> 0
VAR CurrentCustomer =
    SELECTEDVALUE ( Table3[Customer] )
RETURN
    IF (
        NOT ISBLANK ( CurrentCustomer ),
        VAR Customer_Allselected =
            CALCULATETABLE ( VALUES ( Table3[Customer] ), ALLSELECTED ( Table3 ) )
        VAR Customer_Allselected_ValueA_Zero =
            FILTER ( Customer_Allselected, CALCULATE ( SUM ( Table1[ValueA] ) ) <> 0 )
        VAR Customer_Top2_With_ValueA_Zero =
            TOPN (
                2,
                Customer_Allselected_ValueA_Zero,
                CALCULATE ( SUM ( Table2[ValueB] ) ), DESC
            )
        RETURN
            IF (
                CONTAINS ( Customer_Top2_With_ValueA_Zero, Table3[Customer], CurrentCustomer ),
                1
            )
    )

There could be other ways of writing this, but this worked for me in a model using your sample data.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

Hi @OwenAuger 
That's absolutely stunning. Thank you so much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.