filter IDs using dax

[Anonymous]

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

[PaulDBrown]

@Anonymous 

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

[Anonymous]

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

@Anonymous 

It should work. Here is a random example:

 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.

[Fowmy]

@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

[Anonymous]

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

[Fowmy · Accepted solution]

@Anonymous 

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

[Anonymous]

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]

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

[Anonymous]

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]

@Anonymous 

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