cancel
Showing results for 
Search instead for 
Did you mean: 
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 Model.PNGData Model

Actual Outcome.PNGCurrent ouctome of "TEST R1M" measureDesired Outcome.PNGMockup of desired outcome of "TEST R1M" measure

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: DAX - Monthly Totals Measure

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
Microsoft
Microsoft

Re: DAX - Monthly Totals Measure

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

AndrewKent Helper I
Helper I

Re: DAX - Monthly Totals Measure

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors