Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndrewKent
Helper I
Helper I

DAX - Monthly Totals Measure

Hi all,

 

I'm new to the forum and Dax and I'm hoping someone can help a measure. I have two tables of data "qry_data_opportunities" and "qry_period_matrix". Both are linked in my data model via a numeric "Period ID". I am looking to amend my measure so that I get the desired effect of showing "Period ID" where the count is 0, rather than only the "Period ID" where there was a value. The DAX that I have so far is;

 

TEST R1M :=
COUNTAX (
    FILTER (
        ALLSELECTED ( qry_data_opportunities ),
        qry_data_opportunities[Period ID] = MAX ( qry_data_opportunities[Period ID] )
    ),
    [Employee ID]
)

 

Can anyone help me modify this so I get the desired outcome? I have attached some screenshots which will hopefully articulate what I am trying to do.

 

Note: I'm doing all of my testing in PowerPivot before moving the model into PowerBI.

 

Thanks,

 

Andy

 

Data ModelData Model

Current ouctome of "TEST R1M" measureCurrent ouctome of "TEST R1M" measureMockup of desired outcome of "TEST R1M" measureMockup of desired outcome of "TEST R1M" measure

1 ACCEPTED SOLUTION
v-micsh-msft
Employee
Employee

Hi AndrewKent,

 

in Power BI Desktop, under the Fields in customization pane, click on the value dropdown list, it will show an option "Show items with no data", check this would make the visuals to display the items which have the 0 counts.

11.PNG

Or we could add an if in the measure:

TEST R1M :=
If(
COUNTAX (
    FILTER (
        ALLSELECTED ( qry_data_opportunities ),
        qry_data_opportunities[Period ID] = MAX ( qry_data_opportunities[Period ID] )
    ),
    [Employee ID]
)=0,
0,
TEST R1M :=
COUNTAX (
    FILTER (
        ALLSELECTED ( qry_data_opportunities ),
        qry_data_opportunities[Period ID] = MAX ( qry_data_opportunities[Period ID] )
    ),
    [Employee ID]
)
)

This should show the items which have no data.

Post back if you need any further assistance on this.

Regards

View solution in original post

2 REPLIES 2
v-micsh-msft
Employee
Employee

Hi AndrewKent,

 

in Power BI Desktop, under the Fields in customization pane, click on the value dropdown list, it will show an option "Show items with no data", check this would make the visuals to display the items which have the 0 counts.

11.PNG

Or we could add an if in the measure:

TEST R1M :=
If(
COUNTAX (
    FILTER (
        ALLSELECTED ( qry_data_opportunities ),
        qry_data_opportunities[Period ID] = MAX ( qry_data_opportunities[Period ID] )
    ),
    [Employee ID]
)=0,
0,
TEST R1M :=
COUNTAX (
    FILTER (
        ALLSELECTED ( qry_data_opportunities ),
        qry_data_opportunities[Period ID] = MAX ( qry_data_opportunities[Period ID] )
    ),
    [Employee ID]
)
)

This should show the items which have no data.

Post back if you need any further assistance on this.

Regards

This was exactly what I was looking for. As I was testing in PowerPivot, it made more sense to use the IF statement (which for whatever reason I didn't think of using in DAX eventhough it is invaluable in Excel!)

 

Thanks again.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.