Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dev13
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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

@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???

PaulDBrown
Community Champion
Community Champion

@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.






Fowmy
Super User
Super User

@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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

 

amitchandak
Super User
Super User

@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
)

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

Fowmy
Super User
Super User

@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! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.