March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I am trying to display two zero (0) value for a line where it has column data and measure values.
I have this bottom measure which does not display anything because there is no value for [Ave Census].
Currently, vw_census_trend_MonthToDate_2[Ave Census] is a column data, and there is no value on this column.
I also tried something like this, but no success:
What is best approach/solution?
Solved! Go to Solution.
I would prefer you don't do that. Instead, enable "Show items with no data".
Hi, You might use COALESCE function to display a specific value (such as zero) when there is no value. so it can be like: CALCULATE(SELECTEDVALUE(COALESCE([YourOriginalMeasure], 0)))
Thank you for your feedback.
I tried with Coalesce. but I am getting this error: "Parameter is not the correct type" when I hover over the red underlined area.
And also getting this message on the bottom: Column " cannot be found or may not be used in this expression:
This is original measure:
The data format of the measure "Skill Mix % All" is Percentage.
Thank you so much for your help again! 🙂
I uploaded my concised and data-safe version into my Google drive here .
There are three tabs/parts ( Actual, Variance and Budget) on this PBI file.
What you would see here is that because "Kyle" does not have "Census Data" for Actual, it would not reflect on the other visuals in both Actual and Variance tabs.
Is it possible to show 0s (on the missing lines)?
Thanks.
You need to do a lot of work on your data model before you can think of that
You need to create a Facilities dimension table and then merge it with the trend and occupancy tables. Then you need another PayerType dimension , and then youcan connect your dimensions to your fact without the need for a joinkey table.
Can you please take a look at my updated PBI file here that has two additional dim tables (tblFacility & tblPayerType)? I deleted all relationships from my previous "JoinKey" table.
I am not sure how to connect tblFacility with 3 fact tables.
Actually, I am lost in many ways 🙂
Also, I am not sure why on the "Actual" tab, it shows fewer columns (4 instead of 6) than others.
Appreciated.
Merge these tables
Bidirectional 1:1 between two tables usually indicates that this should be a single table.
This is not yet a dimension table. It is missing a primary key. Figure out what the primary key is and kick out/downstream anything that prevents it from working.
Avoid bidirectional *:* relationships wherever possible. They are expensive and can lead to wrong results.
I have been modifying relationships between dim table (especially "zzvw_occupancy_trend") with three fact tables for last day or so.
Here is my PBI file.
I am still having a difficulty with using a Filter (Facility Name).
I am not sure whether I need to bring value from dim table or Fact table to be used for filter.
Can you please take a look at the relationship that I created first and give me any feedback where I am wrong?
Appreciated for your help again.
This looks great. Filters should in the vast majority of scenarios come from the dimension tables. That filter choice then flows down into the fact tables. If you filter on a fact column that choice can not (and should not) propagate back up into the dimension.
Ok. Thank you so much for your feedback.
I will work on it again tomorrow and let you know 🙂
I was working pretty much all day with relationships.
I thought that "joinkey" table would work, but I will definitely restructure my relationship tomorrow based on your suggestions and let you know where I am at. Thank you so much!!
BTW, what do you mean by "merge"?
Do I have to combine two tables (like "trend" and "occupancy" tables) or you meant connect with Facility dim table with "trend" and "occupancy" tables?
The best approach is not to do this. The blanks are there for a reason, and filling them with 0 skews the data.
The second best approach is to use disconnected tables and cross joins.
Thank you so much for your feedback. I think first option is not my option at this moment.
How do we go about doing with the second options (using disconnected tables and cross joins)?
Thanks.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
27 | |
27 | |
21 | |
19 |