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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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)
)
)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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