Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I brought in indexes/columns into my Invoice table to create relationships to other tables. Also brought in a date table to connect holiday day table to it and connect data table to Invoice.
I need to be able to filter on holidays etc. Is my model correct? see: https://drive.google.com/file/d/1CBpFLkw37E5aeYSG9RXMLTzOXUCdcn4m/view?usp=sharing
No, not quite. You should bring your Holiday column into the calendar table. You have a 1:1 relationship there. Anytime you have a 1:1 relationship you really have 1 table split in two. Merge them.
Then you need to mark your calendar table as a date table.
Finally, turn off auto time intelligence.
You should turn it off in Global settings to so it isn't on anymore - always use your own date table.
Lastly I'd recommend you build your date table in Power Query vs using DAX. Creating a Dynamic Date Table in Power Query - it avoids issues you will eventually get with calculated columns the more you add, plus, Power Query is designed for transforming data and it has a TON of rich date table features. Here is the PQ date table I am currently using: https://bit.ly/DateTableByEd
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIs my link from Pholidays to Calendar table not enough? Can you make the adjustments and please send me the pbix file?
I don't have access to your source data, so when I go into Power Query, which is where I would do this, I get a bunch of errors.
The date table is a special table in Power BI and it allows you to use Time Intellince functions with it, like PREVIOUSYEAR, DATESBETWEEN, SAMEPERIODLASTYEAR etc.
I've explained what needs to be done. Maybe someone else will do this for you, but if you will take the time to read the links I've sent and these posts, you can do it yourself. The Date table is of incredible importance to Power BI long term, so you should learn these concepts and how to build the table, not just ask for someone to do it for you, because you will be right back in the same place on the next model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you. Please confirm if my star schema is correct and suitable for filtering and cross filtering among tables? https://drive.google.com/file/d/1CBpFLkw37E5aeYSG9RXMLTzOXUCdcn4m/view?usp=sharing
That should work. I would hide the PHolidays table so you don't inadvertently use it. Ideally you would do this merge in Power Query and never bring in the PHoliday table. But with that table hidden and not used for any future measures or visuals, that is a good Star Schema model. 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.