Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
StidifordN
Helper III
Helper III

Summarized Table but all date periods

Hi all,

 

I have a new table being created and all is working correctly - except when - there is no data for a month period in the Driver Behaviour data but there is an infringement dated within that period.   In this scenario, the table does not display a row for the month, and therefor doesn't lookup the infringement that existed in the period.  In this scenario it is Nov 2021.

 

TableData1.jpg

 

 

 

 

 

 


Can the below be adjusted to show ALL month periods -  either

  • between the earliest and latest that exist in the Driver Behaviour table, or
  • between the earliest and latest that exist in the Dates table, or
  • between Jan 1 2019 and the end of the previous month??

 

Driver Scoring Draft = 
SUMMARIZE(
    DriverBehaviour,
    DriverBehaviour[Driver],
    Dates[Date (bins) Month Period],
    "Total Style Score",
        CALCULATE(SUM(DriverBehaviour[Driver Style Points])),
    "Total KM Travelled",
        CALCULATE(SUM(DriverBehaviour[Trip Distance Final])),
    "Infringements Count",
        CALCULATE(COUNTROWS(FILTER(InfringementData,InfringementData[Penalty Type]="Speeding or Red light Camera")),
        RELATEDTABLE(DriverBehaviour)
        )
        )

 

 

4 REPLIES 4
v-tianyich-msft
Community Support
Community Support

Hi @StidifordN ,

 

You can choose to show items with no data, if it doesn't work, please provide a sample to better solve the problem for you.

vtianyichmsft_1-1701155263448.png

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thanks for the response @v-tianyich-msft  - but this isn't a visual as yet, this issue is in attempting to create the table data.  

amitchandak
Super User
Super User

@StidifordN ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Try if this can help

 

Driver Scoring Draft =
SUMMARIZE(
Dates, // Assuming 'Dates' is your calendar table
Dates[Date (bins) Month Period],
"Driver", FIRSTNONBLANK(DriverBehaviour[Driver], "Unknown"), // Replace "Unknown" with a suitable default or blank
"Total Style Score",
CALCULATE(SUM(DriverBehaviour[Driver Style Points]), DriverBehaviour),
"Total KM Travelled",
CALCULATE(SUM(DriverBehaviour[Trip Distance Final]), DriverBehaviour),
"Infringements Count",
CALCULATE(
COUNTROWS(
FILTER(
InfringementData,
InfringementData[Penalty Type] = "Speeding or Red light Camera"
)
),
RELATEDTABLE(DriverBehaviour)
)
)

Thanks for the response @amitchandak 

Whilst this did result in every month being displated regardless of data, Unfortunately it also resulted in just the first name to appear per month.  

 

Data1.jpg

 

I will create a sample pbix file and respond shortly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.