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
gzai
Frequent Visitor

Set default data based on specific rule filter

Hi all,

I have table :

 

Table_A

ClientTypeCategoryValue
Client A  A  AA10
Client B  B  AA15
Client B  A  AB8
Client C  C  AC5
Client A  D  AB13
Client D  E  AD4
Client E  F  AE7

 

Table_B ( duplicate from Table_A, remove other column except column Type, and set only specific Type )

Type
  B
  C
  E

Table_A & Table_B have relationship many to one based on column Type

 

For visual I want to add slicer for column Type ( Multiselect ) and chart data based on Category. 
Slicer Type ( from Table_A )

------------

A

B

C

D

E

F

 

I have measure in Table_A to detect slicer Type when selected ( True / False ).

Filtered Type = CALCULATE(ISFILTERED(Table_A[Type]), ALLSELECTED(Table_A[Type]))

 

How to create a measure based on measure I've made,
when slicer Type is not selected, chart data only show default data based on all type in Table_B, 

and when slicer Type is selected single or multiple, chart data show based on selected.

 

Any Suggestion?

 

1 ACCEPTED SOLUTION
gzai
Frequent Visitor

After trying several times,


when using your measure, it's actually correct. but there is a little bug when using isfiltered. isfiltered will return true when nothing is selected (don't know how).


I finally combined your measure with measure I've made and make some changes.

 

Measure : 

 

Filtered Type =
IF(
CALCULATE(ISFILTERED(Table_A[Type]), ALLSELECTED(Table_A[Type])),
CALCULATE(SUM(Table_A[Value]),
ALLSELECTED(Table_A[Type])
),
CALCULATE(SUM(Table_A[Value]),
FILTER(Table_A, Table_A[Type] in ALLSELECTED(Table_B[Type]) )
)
)
 
Thanks @amitchandak 

View solution in original post

5 REPLIES 5
gzai
Frequent Visitor

After trying several times,


when using your measure, it's actually correct. but there is a little bug when using isfiltered. isfiltered will return true when nothing is selected (don't know how).


I finally combined your measure with measure I've made and make some changes.

 

Measure : 

 

Filtered Type =
IF(
CALCULATE(ISFILTERED(Table_A[Type]), ALLSELECTED(Table_A[Type])),
CALCULATE(SUM(Table_A[Value]),
ALLSELECTED(Table_A[Type])
),
CALCULATE(SUM(Table_A[Value]),
FILTER(Table_A, Table_A[Type] in ALLSELECTED(Table_B[Type]) )
)
)
 
Thanks @amitchandak 
amitchandak
Super User
Super User

@gzai , Try a measure like


Filtered Type = if(ISFILTERED(Table_A[Type]) , CALCULATE(count(Table_A[Type]), ALLSELECTED(Table_A[Type])) ,
CALCULATE(count(Table_A[Type]), filter(Table_A , Table_A[Type] in Table_B[Type])))

Hi @amitchandak ,

 

I have tried measure

Filtered Type = if(ISFILTERED(Table_A[Type]) , CALCULATE(count(Table_A[Type]), ALLSELECTED(Table_A[Type])) ,
CALCULATE(count(Table_A[Type]), filter(Table_A , Table_A[Type] in Table_B[Type])))


error "A single value for column 'Type' in table 'Table_B' cannot be determined. ..."

@gzai , Try like

Filtered Type = if(ISFILTERED(Table_A[Type]) , CALCULATE(count(Table_A[Type]), ALLSELECTED(Table_A[Type])) ,
CALCULATE(count(Table_A[Type]), filter(Table_A , Table_A[Type] in allselected(Table_B[Type]))) )

@amitchandak measure no error, but it seems for else logic is not correct.

Visual chart when no selected filter :
Total Value = 62 ( 10 (A) + 15 (B) + 8 (A) + 5 (C) + 13 (D) + 4 (E) + 7 (F) )

expected results when no selected filter :

Total Value = 24 ( 15 (B) + 5 (C) + 4 (E) )

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.