cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
SEunson
Frequent Visitor

View rows counted in CountX function

Hi all, I have a measure which counts the number or rows in a table, depending on a condition e.g.

COUNTX(

    TableA,

     IF(

        AND(

            TableA[Start Date] <= [Month End],

             ISBLANK(TableA[End Date]) || TableA[End Date] >= [Month Start]

         ),

        TableA[ID]

    )
)

 

These results are then shown on a line graph, with month on the x axis.

 

I would like to visualise the rows that have been counted in a table, so I can see further details on the counted rows.

 

Drill though does not work because the rows counted for a specific month do not necessarily fall within that month.

 

Does anyone have any suggestions?

Thanks!

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Create a new measure like 

Is Visible = IF( 
SELECTEDVALUE('TableA'[Start Date]) <= [Month End] &&
( SELECTEDVALUE('TableA'[End Date]) >= [Month Start] || ISBLANK(SELECTEDVALUE('TableA'[End Date])),
1)

then use that as a filter on your table visual to only show rows when the value is 1

View solution in original post

3 REPLIES 3
SEunson
Frequent Visitor

Hi, I tried your suggestion out. I can't get it to work as a filter. But if I put the new measure in a table along with the other fields I want to see, it seems to work ok then. I'm not too sure why that is... but anyway, it's better than I had before, so thanks.

SEunson
Frequent Visitor

Hi johnt75,
Thanks for the response. I'll have a look into this. Will let you know how I get on.

johnt75
Super User
Super User

Create a new measure like 

Is Visible = IF( 
SELECTEDVALUE('TableA'[Start Date]) <= [Month End] &&
( SELECTEDVALUE('TableA'[End Date]) >= [Month Start] || ISBLANK(SELECTEDVALUE('TableA'[End Date])),
1)

then use that as a filter on your table visual to only show rows when the value is 1

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors