Visual shall use specific rows depending on slicer selection

[Patrick1612]

Hello,

I have following issue and would be very happy if some one could help me:

I have a table and I want to count the unique ID's, grouped by their value. Thus I use e.g. a pie chart. However, there is also a data slicer in the report. If this slicer is not used, I want to count all rows which have indicator 1, otherwise the rows with indicator 0. At that point, I don't know how to implement this.

Fact table:

ID	Value	Indicator
1	1	1
1	100	0
2	10	1
2	100	0
3	10	1
3	10	0

I have found a solution by using measures, but I don't want to use it as the drillthrough with a measure do not keep the indicator filter. Additionally, I use the fact table also for other calculations and with measures, I lose the context filtering to other visuals.

Any ideas how to solve it?

Greetings,
Patrick

[amitchandak]

@Patrick1612 , You can use isfiltered 

Meausre =

var _fil = calculate(isfiltered(Table[Date]), allselected(Table[Date]) )
return

if(_fil, countrows(filter(Table, Table[Indicator]= 1) ) , countrows(filter(Table, Table[Indicator]= 0) ) )

HasOneValue , Isfiltered: https://www.youtube.com/watch?v=hXg3kRFSGjA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=38
https://youtu.be/cN8AO3_vmlY?t=10120

[Patrick1612]

Hello @amitchandak ,
thank you for your quick response. Your linked videos where helpful for some other tasks, but do not solve my current issue. As I wrote, I have created a measure in my report counting the IDs. However, I don't want to use it for the reasons written above. I want to use the implicit Power Bi measure Count for my fact table.

Unfortunately, I can't use a filter measure like yours in the filters pane as the value. To be more precisely, something like this:

If this would be possible, it would solve my issue.
Do you have further ideas?

[amitchandak · Accepted solution]

@Patrick1612 , Create on more Table with following Values

Indicator , Flag

1 , Yes

0, No

1, No

Join this with Indicator field of you table Many to Many, and filter direction from this new tables to your table.

Now use this new table in filter

All these videos in playlist uses same concept to solve different issues
https://www.youtube.com/watch?v=C9K8uVfthUU&list=PLPaNVDMhUXGYQJ4Gfrdac9Ua_QJ5FbQhB&index=1

[Patrick1612]

Hello @amitchandak ,
your ideas helped me a lot. Thank you! 🙂

[v-linyulu-msft]

Thanks for the reply from amitchandak , please allow me to provide another insight:

Hi, @Patrick1612 
Thanks for reaching out to the Microsoft fabric community forum.

Could you please let us know if amitchandak’s response resolved your issue? If it did, kindly accept it as the solution.

To facilitate your understanding, I have created the following test data:

1.First, create a calculated table:

CalculationTable = 
DATATABLE (
    "Indicator", INTEGER,
    "Flag", STRING,
    {
        {1, "Yes"},
        {0, "No"},
        {1, "No"}
    }
)

2.Next, establish relationships between the two tables:

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.