cancel
Showing results for 
Search instead for 
Did you mean: 
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])))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
gzai
Frequent Visitor

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]))) )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
gzai
Frequent Visitor

@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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!