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
AndyDD_UK
Helper III
Helper III

Help with DAX 2

In my Power BI report I have a page for 'sales by employee'.

 

Management want to select a period of weeks/month and see if any employees have not made any sales in a week (or more).

 

I have a matrix:

Column A: Tag as 'Pass/Fail'...essentially anyone with a missing week for the period selected is a fail.

Column B: Employee ID

Rows: Month/week 

Value = number of sales

 

An employee can only be a pass or fail. In other words if they have, for the period selected, 3 weeks of sales but one week without they are a fail.

 

Simplified example of table in Cube:

https://docs.google.com/spreadsheets/d/1TPqrXbS_Tbc3dY13q5PMb8QpNWYyzDIO9aN_oibt57Y/edit?usp=sharing

 

My date table has months and weeks

Thanks

1 REPLY 1
BeaBF
Solution Sage
Solution Sage

@AndyDD_UK Hi! o create a Pass/Fail tag for employees who have missed a week of sales, you can create a measure that checks whether an employee has made a sale in all the selected weeks for the period:

 

Pass/Fail =
VAR SelectedWeeks = SELECTEDVALUES('Date'[Week])
VAR SalesWeeks = CALCULATETABLE(
VALUES('Date'[Week]),
FILTER('Sales', 'Sales'[Sales] > 0)
)
RETURN
IF(
COUNTROWS(SalesWeeks) = COUNTROWS(SelectedWeeks),
"Pass",
"Fail"
)

 

You can add this measure to your matrix as the first column (Column A) and the Employee ID column as the second column (Column B). For the rows, you can use the week or month column from your date table. Finally, for the values, use the Sales column from your Sales table.

The resulting matrix will show the Pass/Fail tag for each employee in each week/month for the selected period. If an employee missed a week of sales, they will have a Fail tag for that week/month, and if they made a sale in all selected weeks, they will have a Pass tag for the period.

 

BBF

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.