cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

filter IDs using dax

Hello,

I have a measure tha calculates the count of IDs using COUNTROWS

 

IDs in month =
COUNTROWS(
CALCULATETABLE( VALUES( Table1[ID]),
FILTER( ALL( Calendar_table),
Calendar_table[date] = some_date),
Table1[date] > another_date
)
 
This works fine but I would also like to see in a different visual, these specific IDs as well instead of only their COUNT.
If I remove the COUNTROWS function I get the error "A table of multiple values was supplied where a single value was expected".
How can I do this?
Thank you
 
1 ACCEPTED SOLUTION

@Dev13 

Try now please:

 

IDs in month = 

CONCATENATEX(
    CALCULATETABLE( 
        VALUES( Table1[ID]),
        FILTER( 
            ALL( Calendar_table),
            Calendar_table[date] = some_date
        ),
        Table1[date] > another_date
    )   
,
Table1[ID],
 UNICHAR(10)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

10 REPLIES 10
Super User II
Super User II

@Dev13 

Create a table visual with only the field Table1[ID].

With the visual selected, go to the filter pane and add the measure [IDs in Month] to the "Filters for this visual". 

In the dropdown, select the option to set the value of the measure to 1 ("is" 1).

The table will list the corresponfing IDs





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown Thank you for your time as well.

The solution you are proposing does not seem to work on my report.

The table visual with only the field Table1[ID] has been loading for about 20 mins without result. 

My measure is indeed a bit more complex than the one I posted but the idea is the same.

It does include some IFs and other measures in its calculation but the result is just this COUNT of IDs.

 

Using simple logic I'm thinking that since it calculates the COUNT fast enough, why can it not show the IDs that make up that count?

I mean the measure finds them and then counts them to sjow me a number.

Why can it not show me these IDs as well?

Any other thoughts on how to do it???

@Dev13 

It should work. Here is a random example:

Measure for filter pane.JPG

 You may be having problems if the data table is huge though. If so, try writing the Countrows measure by filtering the values of the dimension table for the field.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Super User IV
Super User IV

@Dev13

Try this please:

IDs in month = 

CONCATENATEX(
    CALCULATETABLE( 
        VALUES( Table1[ID]),
        FILTER( 
            ALL( Calendar_table),
            Calendar_table[date] = some_date
        ),
        Table1[date] > another_date
    )   
,
Table1[ID],
 "|"
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube Linkedin

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

@Fowmy This is a lot closer to what I was looking for. It would be great if I could show the IDs in a table/matrix visual one on each row instead of a very large concatenated text. 

Is this possible in some way?

Thank you very much for the precious help.

 

@Dev13 

Try now please:

 

IDs in month = 

CONCATENATEX(
    CALCULATETABLE( 
        VALUES( Table1[ID]),
        FILTER( 
            ALL( Calendar_table),
            Calendar_table[date] = some_date
        ),
        Table1[date] > another_date
    )   
,
Table1[ID],
 UNICHAR(10)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Thanx again @Fowmy !

I really appreciate the help!

I have found that instead of using UNICHAR(10), you can also put an "enter" in the text (with Shift+enter) and it has the same result as the  UNICHAR(10). This is in general for DAX. 

So in your first solution all I needed was to replace the  "|" with  " (shift+enter) " between the " ".

Thank you.

 

Super User IV
Super User IV

@Dev13 , Try measure

 

IDs in month =
calculate(
count( Table1[ID]),
FILTER( ALL( Calendar_table),
Calendar_table[date] = some_date),
Table1[date] > another_date
)

or
IDs in month =
calculate(
distinctcount( Table1[ID]),
FILTER( ALL( Calendar_table),
Calendar_table[date] = some_date),
Table1[date] > another_date
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Maybe you did not understand me correctly.

I want to see the specific IDs and NOT their COUNT.

for example if the count is 15, I would like to see these 15 IDs.

in other words analyse which IDs make up this count of 15

Super User IV
Super User IV

@Dev13 

 

COUNTROWS only accept tables but COUNT accepts a column you can use COUNT(TABLE[COLUMN])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

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

Top Solution Authors
Top Kudoed Authors