cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAXRichArd Member
Member

Star Schema - create table with no blank rows

Relationships.JPGASM Dashboard.JPGASM report.JPGCreateTableNoBlankRows.JPG

Photos

  1. Relationship view (Merged Table left side, red arrow (referred to as Master Table), Start Schema for first section of report (center), begining of new Star Schema fact table (bottom left), and attempt at creating a fact table with no blanks (far right center, red arrow).
  2. First section of report from current star schema.
  3. Desire visual to be created from second fact table (it will be a series of visuals, not just one).
  4. One of many attempts to create second fact table with no blanks in Houston Destination column.

 

 

Hello,

I've hacked at the forum and found similar issues but not one that directly solves my problem.

Data

All my data is commercial aviation data. The facts and dimesions have geographic direction (arrivals and departures).

Objective

Create new Star Schema using dimensions and facts from a master table.

 

Merged Table

My Merged Table (see attached photo) is a 'master table' created in query from a series of appended tables from various sources that contain similar dimensions but different facts.

  • I've created 'helper columns' in the merged table. These helper columns play a very important role in organizing my dimenstions in order to run calculations on the facts.

Desired Outcome

Convert an Excel based monthly report dashboard into a PowerBI dashboard (with correct calculations).

 

Current State

Using the Merged Table, I created a Star Schema called FT (Fact Table). I created a fact table and a series of dimension tables.

  • I'm using this approach because it's less confusing to me and I'm creating fewer 'logic' errors.
  • I've created a series of visuals (dashboard pages) for a secction of my report. I am now ready to create another section of the report.

 Next Step (here is where I need help)

I would like  to create a second Star Schema for another section of my report.

  • I'm starting my fact table and am bringing in columns from my master table (merged table).
  • One column has blanks, but for this fact table, the rows with blanks are meaningless and won't be used.

 

Problem Statement

I've created my fact table 'FT Airline Destinations'. The table is good but it has blanks in column 'Houston Destination'.

  • I've experimented with logic functions IF, ISBLANK, ALLNOBLANKROW and possibly others.
  • My fundimental problem is, I don't really know what I'm doing. I read doumentation on various functions I've attempted, and have looked at other examples using various functions but nothing is working for me.

 

Desired Solution

Create a fact table from my master table without blank rows.

 

Thank you in advance for all your guidance and support.

 

Rich Ard

Houston, Texas

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Star Schema - create table with no blank rows

Hi @DAXRichArd,

 

Would you please provide some dummy data for test?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

DAXRichArd Member
Member

Re: Star Schema - create table with no blank rows

Greetings Yuliana Gu!

 

First, Thank you so much for your offer.

Second, I must offer an apology. Even though I proof read my call for help before posting, I un-intentionally mis-represented the problem. The rows were not completely blank. I didn't need the blanks that were being brought in when I began creating the fact table.

 

My fact table has matured since my request and I've since then solved my problem.

The problem originally stemmed from me bringing in data that I thought I would use in the fact table. I ended up not using it.

I simply excluded it.

 

I'm sorry for using your valuable time and I am grateful for your offer to help!

 

Richard

Houston, Texas

View solution in original post

2 REPLIES 2
Microsoft v-yulgu-msft
Microsoft

Re: Star Schema - create table with no blank rows

Hi @DAXRichArd,

 

Would you please provide some dummy data for test?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
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

DAXRichArd Member
Member

Re: Star Schema - create table with no blank rows

Greetings Yuliana Gu!

 

First, Thank you so much for your offer.

Second, I must offer an apology. Even though I proof read my call for help before posting, I un-intentionally mis-represented the problem. The rows were not completely blank. I didn't need the blanks that were being brought in when I began creating the fact table.

 

My fact table has matured since my request and I've since then solved my problem.

The problem originally stemmed from me bringing in data that I thought I would use in the fact table. I ended up not using it.

I simply excluded it.

 

I'm sorry for using your valuable time and I am grateful for your offer to help!

 

Richard

Houston, Texas

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)