Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slicer / display values with no data

Hello,

I have a slicer filtering on a Market table (a list of the markets where my company is present).

Some values have no market specified (blank cell), because they refer to ALL markets.

How can I have these values displayed when I filter on a specific country?

 

For instance:

I have the following sample data. The market column has a many-to-one relationship to my Market table (used in the slicer):

 

Product         Market

Adam            Argentina

Rose

 

 

Rose product is present in all markets. How can I have it displayed when I filter on "Argentina"?

 

Many thanks

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You may add Index column under Add Column ribbon in Query Editor, then create column in the sample table like DAX below.

    Market_New=

Var LastMarket= CALCULATE(MAX(Table1[Market]),FILTER(ALLSELECTED(Table1),Table1[Index]= EARLIER(Table1[Index])-1  && Table1[Market] <>BLANK() ))

Var CurrentMarket= CALCULATE(MAX(Table1[Market]),FILTER(ALLSELECTED(Table1),Table1[Index]= EARLIER(Table1[Index]) && Table1[Market] <>BLANK() ))

Return

IF(Table1[Market] =BALNK(),  LastMarket, CurrentMarket )

     

    Then you may create relationship with your Market table on the sample table1[Market_New] and 'Market Table'[Market] .

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may add Index column under Add Column ribbon in Query Editor, then create column in the sample table like DAX below.

    Market_New=

Var LastMarket= CALCULATE(MAX(Table1[Market]),FILTER(ALLSELECTED(Table1),Table1[Index]= EARLIER(Table1[Index])-1  && Table1[Market] <>BLANK() ))

Var CurrentMarket= CALCULATE(MAX(Table1[Market]),FILTER(ALLSELECTED(Table1),Table1[Index]= EARLIER(Table1[Index]) && Table1[Market] <>BLANK() ))

Return

IF(Table1[Market] =BALNK(),  LastMarket, CurrentMarket )

     

    Then you may create relationship with your Market table on the sample table1[Market_New] and 'Market Table'[Market] .

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.