Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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
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.
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |