Dynamic filter on this visual based on user

[User232431]

Hi I have table with user login mail, how can we able to apply dynamic filter value on card visual based on user.

for ex : 1. Card-1 : if user "s.kv@vu.com" login Sum(Value) with 1(Number) as filter and Card-2: Sum(Value) with 58(Number)

2. Card-1 : if user "k.g@vu.com" login Avg(Value) with 58(Number) as filter and Card-2: Sum(Value) with 1(Number)

3. Card-1 : if user "j.j@vu.com" login Max(Value) with 1(Number) as filter and Card-2: Sum(Value) with 19(Number)

Country	Value	Number	Mail
US	3	1	s.kv@vu.com
US	5	58	s.kv@vu.com
US	4	58	k.g@vu.com
US	4	1	k.g@vu.com
US	5	1	j.j@vu.com
US	6	19	j.j@vu.com

How can we acheve this dynamically?

[v-priyankata · Accepted solution]

Hi @User232431 

Thank you for reaching out to Microsoft Fabric Community.

Please try below steps it may help you.
1. Create a table like below and it is called "UserCardConfig" 

Mail	Card	Calculation	NumberFilter
s.kv@vu.com	Card-1	SUM	1
s.kv@vu.com	Card-2	SUM	58
k.g@vu.com	Card-1	AVG	58
k.g@vu.com	Card-2	SUM	1
j.j@vu.com	Card-1	MAX	1
j.j@vu.com	Card-2	SUM	19

This table tells Power BI which calculation and filter to apply for each user and card.

2. Now create a measure in Power BI called "UserCardValue" with this DAX

UserCardValue = 
VAR SelectedUser = SELECTEDVALUE(UserCardConfig[Mail])
VAR SelectedCard = SELECTEDVALUE(UserCardConfig[Card])
VAR CalcType = SELECTEDVALUE(UserCardConfig[Calculation])
VAR NumberFilter = SELECTEDVALUE(UserCardConfig[NumberFilter])

VAR FilteredTable =
    FILTER(
        Facts,
        Facts[Mail] = SelectedUser &&
        Facts[Number] = NumberFilter
    )

RETURN
    SWITCH(
        TRUE(),
        CalcType = "SUM", CALCULATE(SUM(Facts[Value]), FilteredTable),
        CalcType = "AVG", CALCULATE(AVERAGE(Facts[Value]), FilteredTable),
        CalcType = "MAX", CALCULATE(MAX(Facts[Value]), FilteredTable),
        BLANK()
    )

3.  Main data table must be called Facts and include the columns Mail, Value, Number.
4.  To set up the visuals, start by adding a Card to your report and use the "UserCardValue" measure in it. Then, filter the card so it only shows data for a specific user—for example, set the Mail field to "s.kv@vu.com" and choose whether it's "Card-1" or "Card-2" based on what you want to display. You’ll need to repeat this for each user and for each of their two cards, using the filters that match the setup in your UserCardConfig table.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[andrewsommer]

Can you clarify if your goal is for only the logged in user to see their stats or do you want card visuals for each user on the same report.

Please mark this post as solution if it helps you. Appreciate Kudos.

[User232431]

Thanks for your reply , want card visuals for each user on the same report.