Do I need start date in lookup table for show names?

[daniegajohnson]

I am new to Power BI and am still trying to work out how things relate between Data Tables and Lookup Tables (Avi Singh's method). I work at a museum that has regularly scheduled public programing in our planetarium theater. Our scheduling database works like this: 1 program has 6 events daily, 6 days a week during the summer and 2 days a week the rest of the year. When I run a query whose result fields are Program Name, Event Name, and Start Date, the query returns 13,815 results. One result for every single program event we've ever scheduled. When I removed the Start Date field, the query shortens down to 557 results, one result for every unique program name and program event name combination. One of the companion data tables for this is my ticket sales query. The other data table with program event start date would be my discount query. Addidng the start date to those queries doesn't add extra rows, just an extra column. My question is, where is the best place to put the start date data? Should it be in both the lookup tables and data tables? My desire in the end is to be able to ask Power BI about specific program events. I would like to ask, on Saturday, January 11, tell me the sales revenue of the 11:30 show.

Any guidance would be greatly appreciated.

Thanks,

Dan

[amitchandak]

It is advised to have a date table in power bi and marked as the date and joined to other tables(Data Table). You should have other related data fields like a month, qtr year, etc in this table.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Please refer to this Power BI Guidelines doc  from Microsoft :

https://docs.microsoft.com/en-us/power-bi/guidance/ 

Prefer to transaction(Fact) and master(dimension) table.  And try to solve your queries from there.

In case you want to copy some data from one to the table to another you can do like given below

New Column in table 1 =minx(filter(table2,table1[col1] =table2[col1] && table1[col2] =table2[col2]),table1[col4])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[JarroVGIT]

Hi @daniegajohnson 

My advice would be to add the start datetime to the queries with all the unique shows. This is a dimension table and gives you information on 'things' (in this case, shows). Every unique show gets its own row. The sales table is a fact table and contains transactional data. Typically, these tables are related by using a unique identifier for each show. Power BI can create a relationship between the fact table(s) and the dimensiontable(s). This way you can create measures that reflect SUM(fact[Sales Amount]) with filters on the Dimensiontable on which show specifically. Or cooler stuff, like filtering the the dimension table on only 3rd shows of the day and then calculate the average. You can then do some nice analytics per show based on seasonality, time of day, etc. 

To learn more about dimension tables and fact tables and how to model your data, look for 'datamodelling star model'  or 'datamodelling dimensional'. There is a lot of (free) material on data modelling techniques on the internet fortunately!

Let me know if this helps you 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[daniegajohnson]

@JarroVGITthanks for the reply. I built a dimension table that had program name (daytime show or evening show), show name, show start date, show start time, and show ID (unique alpha-numeric string). I then tried to connect it to my fact table, which only had the program name, show name, and show ID, and I got this error: "This relationship has cardinality Many-Many. This should only be used if it is expected that neither column (Show ID and Show ID) contains unique values, and that the significantly different behavior of Many-many relationships is understood."

Not sure what I'm doing wrong. There are no duplicate values in the dimension table column for Show ID. I checked. Any advice?

I just noticed, there is a program in my fact table that doesn't have a show ID. Our general admission (to visit our grounds and museum wings) program doesn't have shows. Should I try taking out any programs that don't have shows?

[JarroVGIT · Accepted solution]

That seems weird as Power BI does detect a double value. Even if you column only has 1 duplicate, it is not a unique value anymore. A likely scenario is that you have some bogus rows in you dimension table with BLANK values. Two rows with blank value for Show ID will result in a non-unique table. 

If you want you can share you PBIX with me (here or via PM if you don't want to share publicly) and I can have a look for you 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂