Numeric ID (so never "sum", always "count") and LookUp Tags for

Hi Suzanna,

Great questions—these are very common when starting with Power BI, and you’re thinking about the right things already
________________________________________
1. Numeric ID → Do you need to convert to Text?
Short answer: No, you don’t need to convert it to text.
Power BI automatically assigns a default aggregation (usually Sum) to numeric columns—but you can override this.
Best practice:
Keep your ID as a number, but set it to “Don’t summarize” or always use Count.
How to fix it:
• Select your column in the Fields pane
• Go to Column tools → Default summarization
• Choose:
o Don’t summarize (recommended), OR
o Leave it and explicitly use Count
________________________________________
How to count IDs in visuals
When you add your ID to a visual:
Option 1: Quick way
• Drag the ID field into a visual
• Click the dropdown on the field
• Select:
o Count (counts rows)
o Distinct count (counts unique IDs ideal for unique keys)
Since your IDs are unique, you should normally use: Distinct Count of ID
________________________________________
Option 2: Create a measure (best practice)
Create a reusable measure:
Count of Records = COUNT('YourTable'[ID])
Or (better, for unique IDs):
Distinct Records = DISTINCTCOUNT('YourTable'[ID])
Use this measure in all visuals for consistency.
________________________________________ 2. Period Week (PW) tagging for multiple dates
You’ve got:
• 3 different date columns (events)
• A custom calendar (Periods & Weeks, starting April)
________________________________________ Don’t create separate measures for this
Measures are for calculations—not for assigning date attributes like PW.
________________________________________Best practice: Use a Date (Calendar) table
You should create a Date dimension table with:
• Date
• Year
• Period (P01–P13)
• Week (W1–W4)
• Combined PW (e.g., P02W4)
________________________________________
Step 1: Create a Date table
Example (simplified):
DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2023,4,1), DATE(2026,3,31)),
"Year", YEAR([Date]),
"Month", MONTH([Date])
)
``
Then add your custom Period/Week logic based on your railway calendar.
________________________________________
Step 2: Create relationships (IMPORTANT)
Since you have 3 date columns, you do this:
• Create one Date table
• Create 3 relationships:
o DateTable → EventDate1
o DateTable → EventDate2
o DateTable → EventDate3
Only one can be active at a time!
________________________________________
Step 3: Use USERELATIONSHIP for each event
Example:
Count Event 1 =
CALCULATE(
DISTINCTCOUNT('YourTable'[ID]),
USERELATIONSHIP('YourTable'[EventDate1], 'DateTable'[Date])
)
Repeat for Event 2 and Event 3.
Now your PW filters will work correctly depending on which measure you use.
________________________________________
Alternative: Simpler (but less flexible)
If you don’t need full flexibility:
• Add 3 calculated columns:
o PW_Event1
o PW_Event2
o PW_Event3
But this duplicates logic and is harder to maintain.
________________________________________
Recommendation for your scenario
Use DistinctCount of ID (don’t change to text)
Create one Date table with PW logic
Use USERELATIONSHIP measures for each date column
________________________________________
Quick mental model
• Columns = attributes (PW tags, dates, categories)
• Measures = calculations (counts, totals, KPIs)
________________________________________






Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector