How to get count of rows within table visual

Question

I'm trying to create a card visual in Power BI that displays a statement of either "Table visual is empty" or "Table visual is not empty". In most cases I could write a measure of COUNT(SUMMARIZE([Table],[Col1],[Col2])) to evaluate the number of records and return a result.

However, this visual both contains columns from more than one source table. To my knowledge using COUNT(SUMMARIZE()) does not accommodate more than one table. Likewise, if my table is filtered to 0 rows by a slicer the measure would not respond to the change and would display an incorrect result.

My current measure is:

EmptyTable = IF(COUNT(SUMMARIZE([Table1],[Col1],[Col2]...,[Col9])) = 0
, "Table has no rows", "Table contains rows")

But as I said, this SUMMARIZE statement only accounts for one table, whereas the visual contains fields from two other tables.

Is there a way to count the rows in a table visual while accommodating how it's affected by slicers?

Example dataset: Base State

[Slicer]     [Table Visual]
ID 1 [✓]     ID   Value
ID 2 [✓]     1    10
ID 3 [✓]     2    20
ID 4 [✓]     4    40


[Dax Measure]: "Table Visual Is Not Empty"

Example dataset: Preferred End State

[Slicer]     [Table Visual]
ID 1 [ ]      ID   Value
ID 2 [ ]     
ID 3 [✓]
ID 4 [ ]     


[Dax Measure]: "Table Visual Is Empty"

Base State:

Current State:

Table2 Measure should display "Table2 Visual is empty"

Data Model:

The Dax formula I'm using is :

Table2.Measure = IF(COUNT(Table1[Value])=0 , "Table2 visual is empty", "Table2 visual is not empty")

It will be helpful if you provide data sample and explain your data model (tables and their relations). An image of the table visual would be useful too. — RADO
The data is arbitrary. I'm trying to find a measure to evaluate if a visual has been filtered to 0 rows or not. — PausePause
Thank you for posting the images. It's still not enough - it's necessary to know what the 2 tables you mentioned are, what relationships do they have, what DAX formulas you used to put "Value" into the table, and where the slicer filters are coming from. DAX heavily depends on the underlying data structures, and to write your desired measure, I need to know them. — RADO
I am no asking to see your real data. Post an image of your data model diagram, mark out anything irrelevant, and show your DAX formulas. Or create a mock up that represents the situation properly. — RADO

RADO RADO · Accepted Answer · 2019-05-14T22:59:53

Change your measure to the following:

Table Is Empty = 
   VAR 
     Visible_Rows_Count = COUNTROWS( VALUES(Table2[ID2]))
   RETURN
     IF(Visible_Rows_Count > 0, "Table is not empty", "Table is empty")

Result:

I can't help you with the situation where you have values from more than one table, because it's impossible to answer without knowing what this second table is, and how it's related to the other tables. The key reason:

DAX can not see "visuals". It can only see underlying data and filters.

Visuals exist for you, a human. For DAX, they are only a place where Power BI publishes results of DAX formula. Formula itself has no concept of a "visual". It's only aware of the source data, and relevant filters (which are coming from slicers, table rows and columns, and other visuals on the page).

So, when we say "table is empty", for you it means "table visual is empty". For DAX, it means: "after applying all relevant filters on the page, formula returns no records from the data model".

That's why to answer your question, it's necessary to know data model, DAX formulas, and all relevant filters.

How to get count of rows within table visual

1 Answers