How to structure my Fact Table

[KarlinOz]

I have been reading Reza's blogs on https://radacad.com and so getting good information for how to structure my models. I am creating a new model and would really like to get things right so am asking here, after having searched both the forums here and on radacad.com and not found what I think I need to know.

The project is a Tendering console. My data comes from a single .csv file which I am splitting out to a Dim table and a Fact table. My query is on how best to structure the Fact Table because it seems to be that I either need to include a text field in my fact table or create a numeric column for each value in the text field. This issue arises because I want to slice values in a Dimension column by values in my Calendar dimension. Let me try and explain my project:

Raw table from .csv file:

 ────────── ┌─────────┬─────────────┬─────────────┬────────────────┬────────┬─────────┬──────────────────┐
   Name:    │  RowID  │ QuotationNo │ ClosingDate │ Price/Estimate │ Status │ Outcome │ %ChanceOfWinning │
 ────────── ├─────────┼─────────────┼─────────────┼────────────────┼────────┼─────────┼──────────────────┤
 Data Type: │ WholeNo │ String      │ Date        │ WholeNo        │ String │ String  │ DecimalNo        │
 ────────── └─────────┴─────────────┴─────────────┴────────────────┴────────┴─────────┴──────────────────┘

RowID is row number from the spreadsheet so I can refer back to it if needed.

My First attempt:
DimTable

┌───────┬─────────────┬────────┬─────────┬───────────┐
│ RowID │ QuotationNo │ Status │ Outcome │ TenderKey │
└───────┴─────────────┴────────┴─────────┴───────────┘

TenderKey is generated from concatenating RowID and QuotationNo - I did have one instance of QuotationNo occurring twice and it was necessary to differentiate them.

FactTable

┌───────────┬─────────────┬────────────────┬──────────────────┐
│ TenderKey │ ClosingDate │ Price/Estimate │ %ChanceOfWinning │
└───────────┴─────────────┴────────────────┴──────────────────┘

I related the DimTable to the FactTable 1:*, Single direction (* on the FactTable side) even though the data results in a 1:1 relationship.
I also created a Calendar table and related it 1:*, Single direction (* on the FactTable side) with the relationship going from Calendar:FullDateAlternateKey to FactTable:ClosingDate

This worked mostly. Except I need to slice the Status by date. That is, the client wants to know, in each closing month, how many tenders were submitted, how many are still in draft & how many were not submitted. So I had the problem that I wanted to slice my dimension table by the Calendar table (another dimension table) which I deduce is not a good idea. So what I did was to move my Status field from my DimTable to my FactTable. This actually works nicely.

But! Reza, on his radacad.com website, states that a Fact Table should contain numeric fields. I am including a text field. Although it works, have I moved away from best practice. And if so, what would be the better solution?

My Second attempt:
DimTable

┌───────┬─────────────┬─────────┬───────────┐
│ RowID │ QuotationNo │ Outcome │ TenderKey │
└───────┴─────────────┴─────────┴───────────┘

FactTable

┌───────────┬─────────────┬────────────────┬──────────────────┬────────┐
│ TenderKey │ ClosingDate │ Price/Estimate │ %ChanceOfWinning │ Status │
└───────────┴─────────────┴────────────────┴──────────────────┴────────┘

The other idea I had was to, in my FactTable have a column for each value of my Status text. The Status text has three possibilities - Tendering, Submitted, Not Tendering. I would use binary values, 0 and 1 for each column:

┌───────────┬─────────────┬────────────────┬──────────────────┬───────────┬───────────────┬───────────┐
│ TenderKey │ ClosingDate │ Price/Estimate │ %ChanceOfWinning │ Tendering │ Not Tendering │ Submitted │
└───────────┴─────────────┴────────────────┴──────────────────┴───────────┴───────────────┴───────────┘

That would give me all numeric values in my Fact table but if a new Tendering option is added I will need to add a column to my Fact table. Something tells me that is a little messy.

Appreciate your feedback.

[KarlinOz]

So, what I am trying to achieve is to have a Card which updates with a count of tenders (rows) with a status of Submitted sliced by Year and Month. I am also wanting a graph (not sliced) displaying Number of tenders Submitted, Awarded and Awaiting award per month.

I have created measures for these items and using them for my cards and graph. The issue is that the cards and graphs are only displaying totals, not by Year/Month.

For Example:

Sliced for 2017

Sliced for 2018

In the two shots above I want the figures Number Submitted and Number Tendering to respect the Year slicer. I have checked and both those cards are selected to be sliced by the date slicer.

For my graphs, the top two are what I'm currently getting and the bottom two are what I want to achieve.

Sliced for 2017Sliced for 2018

Sliced for 2017Sliced for 2018

I have created a sample project which shows the issue I am trying to solve and is where the problematic screenshots have been taken from. It is downloadable from Box here.

The Measure I have for Number Submitted, as an example is:

Number Submitted = 
MAX(
    COUNTX(
            FILTER('DimTenders'
            ,LOWER('DimTenders'[Tendering?])="submitted"),'DimTenders'[Tendering?]
    )
    ,0
)

I know the issue is because of how I have written the measures but I just don't know how to solve it. I vaguely recall someone may have said measures are calculated outside the scope of slicers and if so that could be the issue. I think I may have to add some calculated columns to my fact table to solve the issue... I am sure it is simple and has been answered several times here but I just can't seem to find the solution for my particular situation. In the past, I have just had one big table for my data which makes slicing easy but is far from best practice so I'm trying to educate myself on this.

[v-cherch-msft]

Hi @KarlinOz 

You may use RELATED function like below.

Reference:https://business-insights.net/2017/09/02/the-magic-of-related-relatedtable-functions-in-dax/

Measure =
CALCULATE (
    COUNT ( DimTenders[Tendering?] ),
    FILTER (
        ALLSELECTED ( FactTenders ),
        RELATED ( DimTenders[Tendering?] ) = "submitted"
    )
)

Measure 2 = CALCULATE(COUNT(DimTenders[Tendering?]),FILTER(ALLSELECTED(FactTenders),RELATED(DimTenders[Tendering?])="tendering"))

Regards,

[KarlinOz]

Hi @v-cherch-msft ,

Thanks for your response. Indeed, RELATED works perfectly for the Card values however my graph is still only showing a total count of all rows in the table. I'm feeling pretty thick because you have probably given me clues on how to fix that but I am still stuck 

[KarlinOz]

Sorry, I was in a bit of a hurry to get that posted. In the last paragraph when I say

---

a new Tendering option is added...

---

I mean a new Status option = ... but if a new Status option is added...

Also, in my screenshot I have way more fields than what I'm mentioning here. In the screenshot Tendering? is the Status field I am referring to.

[v-cherch-msft]

Hi @KarlinOz 

Could you simplified your sample data and explain more about your expected output?For further,please refer to 

How to Get Your Question Answered Quickly

Regards,

[KarlinOz]

Ok thanks @v-cherch-msft, yes I was too brief. I am adding further info.