How to display default rows in Table/Matrix visual even after Slicers applies to the table data

[DataSpace]

I am trying my best to replicate the data with actual scenario here.

I have data in table DS_main as shown in below snippet:

In case you want to copy, below is same data in text format:

Subscribed Products	Weightage	Stats	Status	CityID	UserID	CategoryID
Maruti	0.8	Yearly	Dynamic	NYC	User01	Vehicle
Suzuki	3	Yearly	Dynamic	NYC	User01	Vehicle
BMW	2	Yearly	Dynamic	NYC	User01	Vehicle
Suzuki	7.5	Yearly	Dynamic	NYC	User02	Vehicle
BMW	2.5	Yearly	Dynamic	NYC	User02	Vehicle
Suzuki	0.5	Yearly	Static	DefaultCity	DefaultUser	Vehicle
BMW	6	Yearly	Static	DefaultCity	DefaultUser	Vehicle
Jeans	1	Yearly	Dynamic	LA1	User03	Clothing
T-Shirt	5.6	Yearly	Dynamic	LA1	User03	Clothing
Shirt	3	Yearly	Dynamic	LA1	User04	Clothing
Jeans	4.5	Yearly	Static	DefaultCity	DefaultUser	Clothing
T-Shirt	3.5	Yearly	Static	DefaultCity	DefaultUser	Clothing
Shirt	2	Yearly	Static	DefaultCity	DefaultUser	Clothing

There are 2 slicers:

1. CityID - showing distinct CityID in slicer from City table which is joined with DS_Main table

2. UserID - showing distinct UserID in slicer from User table which is joined with DS_Main table

Each userID belongs to one CategoryID.

Under each CategoryID, there are some specific default rows for city (DefaultCity) and User (DefaultUser).

For Every selection of slicer(cityid and userID),apart from the data from DS_Main as per slicer, it should also mandatorily show rows which has cityID=DefaultCity and UserID=DefaultUser for respective categoryID.

We need to use matrix visual to visualize data as below:

For slicers CityID = NYC and UserID = User01

From above visuals:

        As per userID slicer=User01, first 3 rows selected for dynamic status for all 3 subcribed products(Maruti, Suzuki, BMW)

        Apart from this, there are 2 rows (status=static) with CityID=DefaultCity and UserID=DefaultUser which need to display for respective categoryID. and hence 0.5 is showing for Suzuki and 6 for BMW. Since there is no Default row for Maruti, it is having 0 weightage under static column.

For slicers CityID = NYC and UserID = User02

From above visuals:

    As per userID slicer=User02, it has only 2 rows, hence showing 7.5 for Suzuki and 2.5 for BMW under Dynamic column. since no rows for Maruti, it is showing 0. Similarly, this also fall under category ' Vehicle), it will show all default rows for this user selection as well. Hence 0.5 for Suzuki, 6 for BMW and 0 for Maruti under Static column.

Similar pattern goes with below examples as well:

For slicers CityID = LA1 and UserID = User03

For slicers CityID = LA1 and UserID = User04

Concern here is:

1. At this time of implementation mode, I am first trying to fetch relevant data in table visual and then I will use Matrix. As per the UserID selection from slicer, relevant data is visible BUT the default rows which should always be visible from respective category are not coming. How to display these default rows in table visual(as of now) or Matrix.

2. Follow up matrix visual query will post later here or in separate post once above query resolves.

Many thanks for you comments, input, suggestion.

[DataSpace]

Hello ALL and @Anonymous ,

I have posted another follow up question to above scenario in a seprate thread here .

If you can please check and comment.

Many thanks

[Anonymous · Accepted solution]

Hi  @DataSpace ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _selectcity=SELECTEDVALUE('City'[CityID])
var _selectuser=SELECTEDVALUE('User'[UserID])
return
IF(
    OR(
    MAX('Table'[UserID]) =_selectuser && MAX('Table'[CityID])=_selectcity ,
    MAX('Table'[UserID]) ="DefaultUser" && MAX('Table'[CityID])="DefaultCity"),1,0
)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[DataSpace]

Many Thanks for your reply. 

This working perfectly when visualizing data in a table visual.

Not sure what is wrong in Matrix..

Will update you..