cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DAXRichArd Regular Visitor
Regular Visitor

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
v-yulgu-msft Super Contributor
Super Contributor

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.
DAXRichArd Regular Visitor
Regular Visitor

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

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

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.
DAXRichArd Regular Visitor
Regular Visitor

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 22 members 954 guests
Please welcome our newest community members: