Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DataSpace
Helper II
Helper II

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

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:

 

Ds_Main1.png

 

 

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

 

Subscribed ProductsWeightageStatsStatusCityIDUserIDCategoryID
Maruti0.8YearlyDynamicNYCUser01Vehicle
Suzuki3YearlyDynamicNYCUser01Vehicle
BMW2YearlyDynamicNYCUser01Vehicle
Suzuki7.5YearlyDynamicNYCUser02Vehicle
BMW2.5YearlyDynamicNYCUser02Vehicle
Suzuki0.5YearlyStaticDefaultCityDefaultUserVehicle
BMW6YearlyStaticDefaultCityDefaultUserVehicle
Jeans1YearlyDynamicLA1User03Clothing
T-Shirt5.6YearlyDynamicLA1User03Clothing
Shirt3YearlyDynamicLA1User04Clothing
Jeans4.5YearlyStaticDefaultCityDefaultUserClothing
T-Shirt3.5YearlyStaticDefaultCityDefaultUserClothing
Shirt2YearlyStaticDefaultCityDefaultUserClothing

 

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

 

NYC1.png

 

 

 

 

 

 

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

 

NYC2.png

 

 

 

 

 

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

 

LA1.png

 

For slicers CityID = LA1 and UserID = User04

LA2.png

 

 

 

 

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vyangliumsft_0-1710126703624.png

3. Result:

vyangliumsft_1-1710126703628.png

 

 

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

View solution in original post

3 REPLIES 3
DataSpace
Helper II
Helper II

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
Not applicable

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.

vyangliumsft_0-1710126703624.png

3. Result:

vyangliumsft_1-1710126703628.png

 

 

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.