Forum Discussion
Dynamic filter on this visual based on user
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 "[email protected]" login Sum(Value) with 1(Number) as filter and Card-2: Sum(Value) with 58(Number)
2. Card-1 : if user "[email protected]" login Avg(Value) with 58(Number) as filter and Card-2: Sum(Value) with 1(Number)
3. Card-1 : if user "[email protected]" login Max(Value) with 1(Number) as filter and Card-2: Sum(Value) with 19(Number)
| Country | Value | Number | |
| US | 3 | 1 | [email protected] |
| US | 5 | 58 | [email protected] |
| US | 4 | 58 | [email protected] |
| US | 4 | 1 | [email protected] |
| US | 5 | 1 | [email protected] |
| US | 6 | 19 | [email protected] |
How can we acheve this dynamically?
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
Card-1
SUM
1
Card-2
SUM
58
Card-1
AVG
58
Card-2
SUM
1
Card-1
MAX
1
Card-2
SUM
19
This table tells Power BI which calculation and filter to apply for each user and card.
- 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()
)- Main data table must be called Facts and include the columns Mail, Value, Number.
- 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 "[email protected]" 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.
3 Replies
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.
Thanks for your reply , want card visuals for each user on the same report.
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
Card-1
SUM
1
Card-2
SUM
58
Card-1
AVG
58
Card-2
SUM
1
Card-1
MAX
1
Card-2
SUM
19
This table tells Power BI which calculation and filter to apply for each user and card.
- 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()
)- Main data table must be called Facts and include the columns Mail, Value, Number.
- 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 "[email protected]" 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.
