Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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
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
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..
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |