Count of rows with filters on multiple table visualizations

[mcomsto]

Hi, 

I am trying to create a count of the rows returned in a set of table visualizations that all from the same base fact table using different measures that contain filters as well.

I have two table visualizations on a single page that return counts of survey responses for specific questions.  I am trying to count the number of rows in the each table viz where the Diff measure is above, equal to, and below zero.  I also have a slicer that filters the two comparable measures.  I have been researching countrows with filters, but I am not able to figure how to have the countrows apply to each individual table visualization.  Any help would be greatly apprieciated.

I am trying to create the two count tables highlighted in the red boxes below. 

[Tad17 · Accepted solution]

Yes. You're on the right track. Make sure your 0's are actual 0's and not "null".

Also, I think you may have the formula flipped. See example 2 in the link: https://docs.microsoft.com/en-us/dax/countx-function-dax

Example 2

The following formula illustrates how to pass a filtered table to COUNTX for the first argument. The formula uses a filter expression to get only the rows in the Product table that meet the condition, ProductSubCategory = "Caps", and then counts the rows in the resulting table that have a list price. The FILTER expression applies to the table Products but uses a value that you look up in the related table, ProductSubCategory.

DAXCopy

=COUNTX(FILTER(Product,RELATED(ProductSubcategory[EnglishProductSubcategoryName])="Caps", Product[ListPrice])  

[Tad17]

So you are essentially trying to combine the two red highlighted tables in the shared image?

[mcomsto]

Thank you for the reply.

I'm trying to create the two highlighted tables, sorry that wasn't clear. 

[Tad17]

Ah. That's easy enough. You can use the Countx as a countif function and/or use the FILTER functions.

COUNTX: https://docs.microsoft.com/en-us/dax/countx-function-dax

FILTER: https://docs.microsoft.com/en-us/dax/filter-function-dax

Also might want to check out the COUNTAX function: https://docs.microsoft.com/en-us/dax/countax-function-dax

[mcomsto]

So I would do something like this for counting the above zero rows?

Q1's Above Zero = COUNTX('Form Response Fact_ScoreSummary', ('Form Response Fact_ScoreSummary'[Comp 2 Form Q1 Score]-'Form Response Fact_ScoreSummary'[Comp 1 Form Q2 Score])>0)

I am getting some error about the countx cannot work with values of type Boolean, but I will track it down. 

Just want to know if I am on the right track.

[Tad17 · Accepted solution]

Yes. You're on the right track. Make sure your 0's are actual 0's and not "null".

Also, I think you may have the formula flipped. See example 2 in the link: https://docs.microsoft.com/en-us/dax/countx-function-dax

Example 2

The following formula illustrates how to pass a filtered table to COUNTX for the first argument. The formula uses a filter expression to get only the rows in the Product table that meet the condition, ProductSubCategory = "Caps", and then counts the rows in the resulting table that have a list price. The FILTER expression applies to the table Products but uses a value that you look up in the related table, ProductSubCategory.

DAXCopy

=COUNTX(FILTER(Product,RELATED(ProductSubcategory[EnglishProductSubcategoryName])="Caps", Product[ListPrice])  

[mcomsto]

@Tad17 , I thought the solution you proposed would work, but it did not.   I think I needed to provide more details of my situation.  I have create a new screenshot with the full scenario.  If you don't mind, would you take a look and let me if you have any ideas on how to solve my problem.  Thank you.

[Tad17]

Can you sharea  screen shot of your data in Power BI and the result you are currently getting?

[mcomsto]

I had a mistake in the Above Zero formula, corrected below:

Above Zero = COUNTX(FILTER('Form Response Fact',('Form Response Fact'[Comp 1 Q1 Form Score] - 'Form Response Fact'[Comp 2 Q1 Form Score]) > 0 ),'Form Response Fact'[AllData.Q1 Score]). 

[mcomsto]

I can't really show a screenshot due to the sensitivity of the data, but I have created a measure called "Above Zero"  the formula is: 

Above Zero = COUNTX(FILTER('Form Response Fact',('Form Response Fact_'[Comp 1 Q1 Form Score] - 'Form Response Fact'[Comp 2 Q1 Form Score])= 0 ),'Form Response Fact_ScoreSummary'[AllData.Q1 Score]).  The Above Zero measure is return a count much greater that what it should.  I think it is count all rows in the base table where AllData.Q1 Score has a value.

If this doesn't help, I will need some time to mock up a pbix with sample data.  Let me know and I will do it, it will just take me a little time.

[Tad17]

Hey @mcomsto 

Without being able to see the data or knowing the format I'm not sure how much I can help, but I believe your logic statement may be the problem. I believe it should be >= "0"

The quotation marks may be all that is necessary, but given that the measure is called "above zero" I figured the inequality needs to be "greater than" (>) or "greater than or equal to" (>=). Depending on the formatting of your data the quotations marks may be necessary to count text values.

[mcomsto]

I have attached a mocked up pbix, and I am getting the wrong count values in the Above and Equal Zero measures.  See attached pbix file.  Thank you.

https://www.dropbox.com/s/gbfb8nmwyp2bkrp/mockupforcountxissue.pbix?dl=0

[Tad17 · Accepted solution]

Hey @mcomsto 

I see the problem now. Because of the way your data is set up you cannot use any of the COUNT functions. you will need to unpivot your data to have a row for the row in your table and then turn your measures into calculated columns. 

Essentially your measures are calculating the different between two rows out of a column. Not two columns in a row. Because of this the FILTER function is keeping the rows that contain a component for the measure that is a possible answer to the COUNT function.

[mcomsto]

@Tad17 , thank you.  You are correct and that makes a lot of sense.  Luckily have the data in two tables, one is pivoted to summarize the scores (column-wise) and the other is unpovited (row-wise).  I will use the row-wise table for these calculations. 

[Tad17]

@mcomsto 

Awesome. Sorry it took me so long to figure that out. I should have picked up on the fact that the data wasn't pivoted correctly for my calculations. I'm sorry my presumptions and oversight cost you time.

[mcomsto]

Ok.  Thank you for the reply.  I will create a mocked up data set and pbix file.