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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

TREATAS missing values

Hello, I have created 2 dashboards (db1,db2) based of 2 different Excel files that shows the performance score (score1, score2) of the employees. Both of the dashboards look mostly the same the only difference is that they display different performance scores. 

 

Previously I wanted to created a "overall" dashboard where both scores would be displayed for a specific employee based a employee name drill through from either one of the initial 2 dashboard pages. 

 

he only way I was able to do this was to use TREATAS, however due to its limitations I had to create 2 "overall" pages

(overall1, overall2) for it to work. For example overall1 would be called up when the user decideds to drill through from db1,score1 values
would all be displayed correctly as db1 and score1 are from the same Excel file. Hence TREATAS would function on score2 to use the employees name
from db1 to filter in its own Excel file. 

 

However the problem now is that there seems to be values missing. As the picture below shows, db1 wont show the score of score2
because at week10 there is no data for score 1 eventhough from db2 we can see that score2 clearly has a value on week10.

 

Capture.PNG

My weeks also cross filter each other in both directions.

 

Thank you in advance to who ever can help me come up with a solution fo this. 

4 REPLIES 4
johnt75
Super User
Super User

Remove the relationship between the tables on the week column. Create a proper date table and link that to both db1 and db2, mark it as a date table and use columns from that table in your visuals.

Also create an employee dimension table by combining the unique values from db1 and db2 and then link that table to both db1 and db2, again use the values from this new table in your visuals. You could create the table in Power Query or in DAX, sample DAX below

Employee dimension =
DISTINCT (
    UNION ( DISTINCT ( 'db1'[Employee] ), DISTINCT ( 'db2'[Employee] ) )
)
Anonymous
Not applicable

Hello thank you for helping me. I have tried your method however there were still missing values. My guess is due to me using TREATAS(?). In order to correctly show score2 based on a name that was selected from bd1, score2 will treat the name selected in db1 as the name selected in db2. Thus if for the particular week score1 does not a value score2 would not be displayed because it was filter based on bd1?

 

Maybe by forcing the line chart to show all the weeks would solve this how ever my weeks are not in date format thus I cant change their type to categorical.

Capture1.PNG

Tried to click the "show items with no data" but it did not do much.

Are you sure that values are missing? Looking at the table you initially posted, in week 10 there is a value of 100% but there is no value for 5, which is exactly what the chart is showing at week 10.

Anonymous
Not applicable

My apologies, this chart does not show the error for w10 

 

Here is the chart that correspondes to the error, the data of the table on the right is based off this chart. 

1.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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