Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there
I need help with DAX.
Have below two tables :
Calendar_Dimension_Table
The_Date Year Month
2020-08-15 2020 August
2020-08-16 2020 August
2020-08-17 2020 August
.............
.............
Sales_Table
Sales_Date Sales_Item Sales_Status Sales_Category
2020-08-15 IT123 OPEN MAJOR
2020-08-15 ITYYY OPEN MAJOR
2020-08-15 IT876 CLOSED MINOR
2020-08-15 IT876 SUBMIT MINOR
2020-08-15 IT876 PROCESS MINOR
2020-08-16 IT123 CLOSED MAJOR
2020-08-17 ITABC OPEN MAJOR
2020-08-17 ITXYZ OPEN MAJOR
.............
.............
Tables are join with The_Date and Sales_Date columns.
I am after DAX that provide number of days per calender month where sales status is not closed or sales category is minor.
Expected output is 2 days for Auguest (because 15th Aug and 17th Aug)
Many Thanks.
Try this measure:
number of days =
CALCULATE (
DISTINCTCOUNT ( Sales_Table[Sales_Date ] ),
FILTER (
Sales_Table,
Sales_Table[ Sales_Status ] <> "CLOSED"
|| Sales_Table[ Sales_Category] = "MINOR"
)
)
Output:
Download file: https://gofile.io/d/nTX5fZ
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Not really. COUNTROWS does not give distinct number of days in a month where condition meet (per my original post requirement). Any idea pls
@kaushikmakadia So then this:
Measure =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER('Sales_Table',[Sales_Status]="OPEN" && [Sales_Category]="MINOR")
"Date",[Sales_Date]
)
)
)
Try not to cross-post please.
@kaushikmakadia Try:
Measure = COUNTROWS(FILTER('Sales_Table',[Sales_Status]="OPEN" && [Sales_Category]="MINOR"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |