Forum Discussion

filexsundeep09's avatar
filexsundeep09
Frequent Visitor
3 years ago
Solved

Based on Slicer Selections data should be show (Strings)

Hi Experts,
Please help me with the below scenario.
I want to display a different level of data in a table visual, 

Slicer Sel =
var A = FIRSTNONBLANK('Region Table'[Comments],COUNT('Regiont Table'[Comments]))
var B = FIRSTNONBLANK('Unit Table'[Comments],COUNT('Unit Table'[Comments]))
var C = FIRSTNONBLANK('Global table '[Comments],COUNT('Global table '[Comments]))

var D = IF(ISFILTERED('Dim Region'[Unit]),B,IF (ISFILTERED('Dim Region'[Region]),A,IF(ISFILTERED('Dim Region'),C)))
return
D


1.If I  am selecting a region in the slicer 
Expecting Out put:  Region table Test data should be displayed in the table visual 
2.If I  am selecting a Unit in the slicer 
Expecting Out put: Unit table Test data should be displayed in the table visual 
3.If I  am not  selecting anything in the slicer 
Expecting Out put : Global table Test data should be displayed in the table visual 


Example 
I have three tables 
Region table:

RegionLine ItemTest data
Americamachine'ssales good 
Europefashionavarge

Unit table:

unitLine itemtest data
clombiomachine'sTest_1
North americafashionTest_2

Global table:

Line ItemTest data
machine'sMachine are good compared to region sale's
fashionHigh sales


i am getting only region level and unit level test data but not getting global level test data (means not filttering anything in the slicer ) please help me on this.
Regards,
Chanti

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi filexsundeep09 

    You can refer to the following example.

    1.Create a new table, the information is as follows:

     

    2.Create a measure:

    Measure = var _add1 = ADDCOLUMNS(ALLSELECTED('Global table'),"Rank",RANKX(ALLSELECTED('Global table'),[Line Item],,ASC,Dense))
var _add2=ADDCOLUMNS(ALLSELECTED(Region),"Rank",RANKX(ALLSELECTED(Region),[Region],,ASC,Dense))
var _add3=ADDCOLUMNS(ALLSELECTED(Unit),"Rank",RANKX(ALLSELECTED(Unit),[unit],,ASC,Dense))
return SWITCH(TRUE(),ISFILTERED(Unit[unit])=FALSE()&&ISFILTERED(Region[Region])=FALSE(),MAXX(FILTER(_add1,[Rank]=MAX('Row'[Row])),[Test data]),ISFILTERED(Unit[unit]),MAXX(FILTER(_add3,[Rank]=MAX('Row'[Row])),[test data]),ISFILTERED(Region[Region]),MAXX(FILTER(_add2,[Rank]=MAX('Row'[Row])),[Test data]))

    Then put the "Row" column and measure to the table visual.

    Output:

     

    Best Regards!

    Yolo Zhu

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi filexsundeep09 

    You can refer to the following example.

    1.Create a new table, the information is as follows:

     

    2.Create a measure:

    Measure = var _add1 = ADDCOLUMNS(ALLSELECTED('Global table'),"Rank",RANKX(ALLSELECTED('Global table'),[Line Item],,ASC,Dense))
var _add2=ADDCOLUMNS(ALLSELECTED(Region),"Rank",RANKX(ALLSELECTED(Region),[Region],,ASC,Dense))
var _add3=ADDCOLUMNS(ALLSELECTED(Unit),"Rank",RANKX(ALLSELECTED(Unit),[unit],,ASC,Dense))
return SWITCH(TRUE(),ISFILTERED(Unit[unit])=FALSE()&&ISFILTERED(Region[Region])=FALSE(),MAXX(FILTER(_add1,[Rank]=MAX('Row'[Row])),[Test data]),ISFILTERED(Unit[unit]),MAXX(FILTER(_add3,[Rank]=MAX('Row'[Row])),[test data]),ISFILTERED(Region[Region]),MAXX(FILTER(_add2,[Rank]=MAX('Row'[Row])),[Test data]))

    Then put the "Row" column and measure to the table visual.

    Output:

     

    Best Regards!

    Yolo Zhu

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