cancel
Showing results for
Did you mean:
Helper I

## DAX Count Number of Days in a given month from a date dimension where other table condition meet

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.

1 ACCEPTED SOLUTION
Super User
``````Measure =
CALCULATE(
DISTINCTCOUNT( SALES[Sales_Date] ),
FILTER(
SUMMARIZE( SALES, SALES[Sales_Date], SALES[Sales_Item] ),
CALCULATE(
NOT ISEMPTY( SALES ),
SALES[Sales_Status] <> "Closed"
|| SALES[Sales_Status] = "Minor"
)
)
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
4 REPLIES 4
Super User
``````Measure =
CALCULATE(
DISTINCTCOUNT( SALES[Sales_Date] ),
FILTER(
SUMMARIZE( SALES, SALES[Sales_Date], SALES[Sales_Item] ),
CALCULATE(
NOT ISEMPTY( SALES ),
SALES[Sales_Status] <> "Closed"
|| SALES[Sales_Status] = "Minor"
)
)
)``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

Not really. COUNTROWS does not give distinct number of days in a month where condition meet (per my original post requirement). Any idea pls

Super User

``````Measure =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER('Sales_Table',[Sales_Status]="OPEN" && [Sales_Category]="MINOR")
"Date",[Sales_Date]
)
)
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

``Measure = COUNTROWS(FILTER('Sales_Table',[Sales_Status]="OPEN" && [Sales_Category]="MINOR"))``

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors