Forum Discussion

gzai's avatar
gzai
Helper I
4 years ago
Solved

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?

 

Need Help
  • gzai's avatar
    gzai
    4 years ago

    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 

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

    • gzai's avatar
      gzai
      Helper I
      4 years ago

      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. ..."

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

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

  • gzai's avatar
    gzai
    Helper I
    4 years ago

    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