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.
Hi Experts,
Here is the sample .pbix report Link to sample file
I need the following help
In the attached Power BI file, has the above (picture) data model
When using the visualizations like table, filter etc what is the best practice and are there any exceptions in using the Fact data vs Dimension data? Please can you correct or confirm my understanding
1. Records like Name, Customer etc should be from the DImension table (Customer, Name, dDate ?)
2. Records like associated data like aggregated functions like Count, Sum, avg, etc are from Fact table (Test Data) ?
3. I created the Max date in the fact table and when I used to map to dimension data, I got the last date the user has accessed but how can i get the Last Customer the salesman (Name) has accessed? In short, How to display the GroupBy of name (with the customer and last date modified in DAX)? with help, I am now aware of doing this in M code
4. In the Dax visualization, How can I format the date to dd/mm/yy instead of details as columns like (Quarter, Year, Month, da etc)
5. The moment I add dimension date "dDate" or "Customer "I get full data instead of unique records
Thanks in Advanced
Solved! Go to Solution.
Hope answers below help. I've also put the attached together based on your file.
Example File
1. Records like Name, Customer etc should be from the DImension table (Customer, Name, dDate ?)
Yes dimension tables should contain anything you want to slice your data by.
I think some of the later issues you're having are due to the two AA rows which forces the relationship to be many many. A dimension should be a list of distinct records. You could sort this in power query with "Remove Duplicates" or create a new table in DAX using DISTINCT.
If it has to be many to many always specify a filter direction rather than leaving it bidirectional.
2. Records like associated data like aggregated functions like Count, Sum, avg, etc are from Fact table (Test Data) ?
Yes. We'd normally say that a fact table contains a description of an event. Always start by asking yourself what does one row represent. This is called the grain.
3. I created the Max date in the fact table and when I used to map to dimension data, I got the last date the user has accessed but how can i get the Last Customer the salesman (Name) has accessed? In short, How to display the GroupBy of name (with the customer and last date modified in DAX)? with help, I am now aware of doing this in M code
In the demo file I moved all your measures to the fact table and hid the rest of the fields so Power Bi formats it as a measure table.
I then created a Last Customer and Latest Date measure:
Last Customer =
VAR LatestVisibleDate = LASTDATE ( 'Test Data'[Last Modified Date] )
RETURN
CALCULATE (
SELECTEDVALUE ( Customer[Customer] ),
CROSSFILTER ( Customer[Customer], 'Test Data'[Customer], Both ),
LatestVisibleDate
)
Latest Date =
MAX ( 'Test Data'[Last Modified Date] )
In your test data the customer is actually listed on the fact table so you could side step the cross filter but normally you'd have to read from dimension.
4. In the Dax visualization, How can I format the date to dd/mm/yy instead of details as columns like (Quarter, Year, Month, da etc)
I rebuilt you a date table and then marked it as a date table which gets rid of the auto date hierachy:
dDate =
VAR EarliestYear = YEAR( MIN( 'Test Data'[Last Modified Date] ) )
VAR LatestYear = YEAR( MAX ( 'Test Data'[Last Modified Date] ) )
VAR BaseCalendar =
CALENDAR(
DATE( EarliestYear, 1, 1 ),
DATE( LatestYear, 12, 31 )
)
RETURN
GENERATE (
BaseCalendar,
VAR RowDate = [Date]
VAR RowYear = YEAR ( [Date] )
VAR RowDay = FORMAT ( RowDate, "mmm" )
VAR RowMonth = MONTH ( RowDate )
VAR RowMonthYear = DATE ( RowYear, RowMonth, 1 )
RETURN
ROW (
"Year", RowYear,
"Day", RowDay,
"Month", RowMonth,
"Month Year", RowMonthYear
)
)
5. The moment I add dimension date "dDate" or "Customer "I get full data instead of unique records.
I'm not 100% sure what you mean by this. Are you refereing to a slicer? Let me know and will give a better answer!
@bcdobbs Thank you very much, This really helps me a lot. You have answered the last question as well. I was adding the customer list from the dimension table of "Customer" expecting the solution which you helped by adding the Measure. Thank you very much.
Hope answers below help. I've also put the attached together based on your file.
Example File
1. Records like Name, Customer etc should be from the DImension table (Customer, Name, dDate ?)
Yes dimension tables should contain anything you want to slice your data by.
I think some of the later issues you're having are due to the two AA rows which forces the relationship to be many many. A dimension should be a list of distinct records. You could sort this in power query with "Remove Duplicates" or create a new table in DAX using DISTINCT.
If it has to be many to many always specify a filter direction rather than leaving it bidirectional.
2. Records like associated data like aggregated functions like Count, Sum, avg, etc are from Fact table (Test Data) ?
Yes. We'd normally say that a fact table contains a description of an event. Always start by asking yourself what does one row represent. This is called the grain.
3. I created the Max date in the fact table and when I used to map to dimension data, I got the last date the user has accessed but how can i get the Last Customer the salesman (Name) has accessed? In short, How to display the GroupBy of name (with the customer and last date modified in DAX)? with help, I am now aware of doing this in M code
In the demo file I moved all your measures to the fact table and hid the rest of the fields so Power Bi formats it as a measure table.
I then created a Last Customer and Latest Date measure:
Last Customer =
VAR LatestVisibleDate = LASTDATE ( 'Test Data'[Last Modified Date] )
RETURN
CALCULATE (
SELECTEDVALUE ( Customer[Customer] ),
CROSSFILTER ( Customer[Customer], 'Test Data'[Customer], Both ),
LatestVisibleDate
)
Latest Date =
MAX ( 'Test Data'[Last Modified Date] )
In your test data the customer is actually listed on the fact table so you could side step the cross filter but normally you'd have to read from dimension.
4. In the Dax visualization, How can I format the date to dd/mm/yy instead of details as columns like (Quarter, Year, Month, da etc)
I rebuilt you a date table and then marked it as a date table which gets rid of the auto date hierachy:
dDate =
VAR EarliestYear = YEAR( MIN( 'Test Data'[Last Modified Date] ) )
VAR LatestYear = YEAR( MAX ( 'Test Data'[Last Modified Date] ) )
VAR BaseCalendar =
CALENDAR(
DATE( EarliestYear, 1, 1 ),
DATE( LatestYear, 12, 31 )
)
RETURN
GENERATE (
BaseCalendar,
VAR RowDate = [Date]
VAR RowYear = YEAR ( [Date] )
VAR RowDay = FORMAT ( RowDate, "mmm" )
VAR RowMonth = MONTH ( RowDate )
VAR RowMonthYear = DATE ( RowYear, RowMonth, 1 )
RETURN
ROW (
"Year", RowYear,
"Day", RowDay,
"Month", RowMonth,
"Month Year", RowMonthYear
)
)
5. The moment I add dimension date "dDate" or "Customer "I get full data instead of unique records.
I'm not 100% sure what you mean by this. Are you refereing to a slicer? Let me know and will give a better answer!
Covering 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.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |