Help me to show with the visualization - only once the measures

[ashmitp869]

Hi,

Is there any way to show the below report ApprovedStdHours and ApprovedStdRate only once

 The report ApprovedHours and Rate is being repeated as the employee has three lines of Allowances. 

How to avoid the ApprovedStdHours  i.e 8.5 hours repeat against every line but show only once.

Here is all the tables with field where is my report coming from
Projects - ProjectId
Plan_Plan_DailyPlanHoursResourceVersion -Display,PlanDate,WBS,WBSDescription
Core_ProjectRateCode -ProjectRateCodeDecsription
Core_Craft – CraftDescription
BT – EmployeeShiftStart, EmployeeShiftEnd
BT_Allowances – Allowances
Relationship provided

DailyPlanHoursResourceVersion (CostItemId) and CostItem(Cost_item_id)
CostItem(Project_id) and Projects(Project_id)
DailyPlanHoursResourceVersion (PlanDate and Date (Date)
DailyPlanHoursResourceVersion (Key) and BT(Key)
BT(Key) and BT_Allowances(Key) one to many Both
Core_ProjectRateCode(ProjectRateCodeId) and BT (DefaultProjectRateCodeId)
Core_Craft(CraftId) and BT (CraftId)
Sharing the dax measure

ApprovedStdHours = 
 CALCULATE(SUM(Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]),Plan_DailyPlanHoursResourceDetailVersion[IsActive] =TRUE(),
Plan_DailyPlanHoursResourceDetailVersion[IsLatest] =TRUE(),
 Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] ="Approved",CostItem[Is_terminal] = TRUE())

Thanks

[ashmitp869]

Option 3 and Option 4 is not possible as they all want they together and as a table format.
I tried to use the option 1 dax formula but its still giving me 

Option 2 is giving error

A single value for column 'Allowances' in table 'BT_Allowances' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a sing

[jaineshp]

Hey @ashmitp869,

Looking at the error message, the issue with Option 2 is that there are multiple allowance values in the filter context. Let me provide you with alternate solutions:

Option 1 (Remove allowance grain):

ApprovedStdHours =
CALCULATE(
SUM(Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]),
Plan_DailyPlanHoursResourceDetailVersion[IsActive] = TRUE(),
Plan_DailyPlanHoursResourceDetailVersion[IsLatest] = TRUE(),
Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved",
CostItem[Is_terminal] = TRUE(),
REMOVEFILTERS(BT_Allowances) // This removes the allowance filter context
)

Option 2 (Show only for first allowance row):

ApprovedStdHours_Display =
VAR FirstAllowance =
CALCULATE(
MIN(BT_Allowances[Allowances]),
ALLEXCEPT(BT_Allowances, BT[Key]) // Keep only employee context
)
VAR CurrentAllowance = MIN(BT_Allowances[Allowances])
RETURN
IF(
CurrentAllowance = FirstAllowance,
[ApprovedStdHours],
BLANK()
)

Option 2 (Using row number approach):

ApprovedStdHours_Display =
VAR CurrentAllowanceRank =
RANKX(
ALLEXCEPT(BT_Allowances, BT[Key]),
BT_Allowances[Allowances],
,
ASC
)
RETURN
IF(
CurrentAllowanceRank = 1,
[ApprovedStdHours],
BLANK()
)

(Create a flag column in BT_Allowances table): If you can modify the data model, add a calculated column in BT_Allowances:

IsFirstAllowance =
IF(
BT_Allowances[Allowances] =
CALCULATE(
MIN(BT_Allowances[Allowances]),
FILTER(BT_Allowances, BT_Allowances[Key] = EARLIER(BT_Allowances[Key]))
),
1,
0
)

Then use this measure:

ApprovedStdHours_Display =
IF(
MAX(BT_Allowances[IsFirstAllowance]) = 1,
[ApprovedStdHours],
BLANK()
)

Corrected Option 1 first as it's the simplest approach. If that doesn't work, try Corrected Option 2. The key difference is using ALLEXCEPT to maintain only the employee context while removing the allowance context.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best regards,
Jainesh Poojara / Power BI Developer

[ashmitp869]

HI @jaineshp 

This dax expression worked

ApprovedStdHoursTest =

VAR FirstAllowance =

CALCULATE(

MIN(BT_Allowances[Allowances]),

ALLEXCEPT(BT_Allowances, BT[Key]) // Keep only employee context

)

VAR CurrentAllowance = MIN(BT_Allowances[Allowances])

RETURN

IF(

CurrentAllowance = FirstAllowance,

[ApprovedStdHours],

BLANK()

)
Although, it only showing the first allowance

Is there any way to show like this
as my user want all the three allowances to be show but Approved Hours should be only once

[jaineshp · Accepted solution]

Hey @ashmitp869,

Looking at your requirement, I can see you want to display the ApprovedStdHours value only once per employee while showing all allowance rows. Here are a few approaches to achieve this:

Option 1: Show value only in the first row, blank in others

ApprovedStdHours_Display =
VAR FirstAllowance =
CALCULATE(
MIN(BT_Allowances[Allowances]),
ALLEXCEPT(BT_Allowances, BT[Key])
)
VAR CurrentAllowance = MIN(BT_Allowances[Allowances])
RETURN
IF(
CurrentAllowance = FirstAllowance,
[ApprovedStdHours],
BLANK()
)

Option 2: Show value in the last row, blank in others

ApprovedStdHours_LastRow =
VAR LastAllowance =
CALCULATE(
MAX(BT_Allowances[Allowances]),
ALLEXCEPT(BT_Allowances, BT[Key])
)
VAR CurrentAllowance = MIN(BT_Allowances[Allowances])
RETURN
IF(
CurrentAllowance = LastAllowance,
[ApprovedStdHours],
BLANK()
)

Option 3: Add visual separator with dashes

ApprovedStdHours_WithSeparator =
VAR FirstAllowance =
CALCULATE(
MIN(BT_Allowances[Allowances]),
ALLEXCEPT(BT_Allowances, BT[Key])
)
VAR CurrentAllowance = MIN(BT_Allowances[Allowances])
VAR ActualValue = [ApprovedStdHours]
RETURN
IF(
CurrentAllowance = FirstAllowance,
ActualValue,
IF(ISBLANK(ActualValue), BLANK(), 0) // or use "---" for text
)

Option 4: Create a summary row approach If your table supports grouping, you could:

1. Group by Employee (BT[Key])
2. Show allowances as detail rows
3. Display ApprovedStdHours at the group level

Recommendation:

• Use Option 1 if you want the value at the top
• Use Option 2 if you want the value at the bottom
• Use Option 3 if you want visual consistency with separators

The current working solution you have is actually Option 1 from above. If you need the value to appear in a different position (like the last allowance row), try Option 2.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best Regards,
Jainesh Poojara | Power BI Developer

[MockIT2As]

6.

[ashmitp869]

This expression worked for me

ApprovedStdHours_WithSeparator =

VAR FirstAllowance =

CALCULATE(

MIN(BT_Allowances[Allowances]),

ALLEXCEPT(BT_Allowances, BT[Key])

)

VAR CurrentAllowance = MIN(BT_Allowances[Allowances])

VAR ActualValue = [ApprovedStdHours]

RETURN

IF(

CurrentAllowance = FirstAllowance,

ActualValue,

IF(ISBLANK(ActualValue), BLANK(), 0) // or use "---" for text

)

[jaineshp]

Hey @ashmitp869,

Looking at your issue, the problem is that your ApprovedStdHours measure is being repeated for each allowance row because of the one-to-many relationship between BT and BT_Allowances.

Here are a few solutions to show the measure only once:

Option 1: Use DISTINCTCOUNT or modify the measure

ApprovedStdHours =
CALCULATE(
SUM(Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]),
Plan_DailyPlanHoursResourceDetailVersion[IsActive] = TRUE(),
Plan_DailyPlanHoursResourceDetailVersion[IsLatest] = TRUE(),
Plan_DailyPlanHoursResourceVersion[DailyPlanStatusDescription] = "Approved",
CostItem[Is_terminal] = TRUE(),
VALUES(BT[Key]) // This will remove the allowance grain
)

Option 2: Create a conditional measure

ApprovedStdHours_Display =
IF(
BT_Allowances[Allowances] = CALCULATE(MIN(BT_Allowances[Allowances]), ALLEXCEPT(BT_Allowances, BT_Allowances[Key])),
[ApprovedStdHours],
BLANK()
)

Option 3: Matrix visual approach

• Put employee details in rows
• Put allowances as a separate row group below employee
• Place ApprovedStdHours only at the employee level, not allowance level
  
  

Option 4: Separate tables in report

• Create one table for employee hours/rates (without allowances)
• Create another table for allowances details
• Link them visually but keep measures separate

The conditional measure (Option 2) is probably your quickest fix - it will show the value only for the first allowance row per employee.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best regards,
Jainesh Poojara / Power BI Developer